SELECT retrieves data from the database.

Note: Performing a SELECT on a large file can cause an ODBC Server abort if the data setting in the /etc/security/limits file is set to a value too small to handle the large file SELECT operation. To avoid this situation, increase the data setting in /etc/security/limits file to a value large enough to handle the large file SELECT operation. The default value for this setting is 256 MB.
SELECT select_list FROM table_list [select_condition [select_condition…]] 

   select_list ::=
      [DISTINCT] {term|function} [AS name] [, {term|function} [AS name]...] 

   table_list ::=
      [account.]table_name [AS name][, [account.]table_name [AS name]...] 

   select_condition ::=
       WHERE search_condition [{AND|OR} search_condition...]
      | GROUP BY column_name [, column_name ...]
      | HAVING search_condition
      | UNION [ALL] select_statement
      | ORDER BY order_item [ASC | DESC] [, order_item [ASC | DESC]...]
      | INTO TABLE table_name 

   term ::=
      [[account.]table_name.]column_name | constant 

   function ::=
      aggregate_function | scalar_function 

   aggregate_function ::=
       AVG ([DISTINCT] expression)
      | COUNT (expression)
      | COUNT (DISTINCT expression)
      | COUNT (*)
      | MIN (expression)
      | MAX (expression)
      | SUM ([DISTINCT] expression})
      expression ::= term [operator {term|expression}] 

   operator ::=
      + | - | * | / | string concatenation(||) 

   search_condition ::=
      [NOT] conditional_expression [{AND|OR} [NOT] conditional_expression...] 

   conditional_expression ::=
       expression operator {term|USER}
      | expression operator {ALL|ANY|SOME}
      | expression [NOT] BETWEEN expression AND expression
      | expression [NOT] IN ({select_statement | value [, value...]})
      | expression [NOT] LIKE value [ESCAPE value]
      | column_name IS [NOT] NULL
      | EXISTS (select_statement)

DISTINCT (syntax part)

By default, all rows in query results are displayed. To exclude duplicate rows, specify DISTINCT, which can be used only once per SELECT clause.

Function (syntax part)

There are two types of functions that can operate on columns in a SELECT statement – aggregate and scalar. Aggregate functions are used to get summary values for the rows selected by the search_condition in a SELECT statement. Since aggregate functions return a single value, they cannot appear in a SELECT statement with typical columns or expressions.

Each of the aggregate functions is defined below:

AVG() Averages a column of numeric data.
AVG(DISTINCT) After eliminating duplicates, averages a column of numeric data
COUNT() Counts the number of select_items in a column.
COUNT(DISTINCT) After eliminating duplicates, counts the number of select_items in a column.
COUNT(*) Counts the number of rows in the query output.
MIN() Determines the smallest value of select_item in a column.
MAX() Determines the largest value of select_item in a column.
SUM() Totals a column of numeric data.
SUM(DISTINCT) After eliminating duplicates, totals a column of numeric data.

There are three types of scalar functions:

  • string-manipulation functions

  • numeric functions

  • time/date functions

See Scalar functions for a complete list of supported scalar functions, by type.

All three types of scalar functions operate on columns from either a table, a constant value, or another scalar function.

Note: A special syntax is required for placing a scalar function in a SELECT statement. The scalar function must be enclosed in {} and preceded by fn, as shown below:
{fn scalar_function(parameters)}


The CONCAT scalar function concatenates two string expressions. In this example assume that the CLIENTS table has separate columns for first and last name. The  statement below creates a query containing the full name of each client:

select {fn CONCAT(first_name, last_name)} from clients

Unfortunately, the query above does not place a space between the names. To place the necessary space between the first and last names, write the statement like this:

select {fn CONCAT({fn CONCAT(first_name, ' ')}, last_name)} from clients

AS name (syntax part)

The optional AS name clause is used to specify an alias name for a table or a column within a table. The alias name will appear in the query output.

Use AS name to alias a column name when there is a select_item that is an expression or that contains a field function, and the column is to be given a name.


select customerid as ID, organizationname as Company from customers


If AS name is used to alias a table, the alias name must be used in place of the table name throughout the SELECT command.

select contactname from customers as C where C.customerid=13

USER (syntax part)

USER is the keyword for the current user account.

Search_condition (syntax part)

search_condition specifies the criteria records must meet to be included in the query results. Multiple search conditions can be connected using the AND/OR operators. The NOT operator can be used to reverse the outcome of a logical expression.

search_condition can take one of the forms below:

Form 1 column1 operator field2
Example amount = 100.00
Form 2 column1 operator column2
Example salesreps.repoffice =
Form 3 column1 operator expression
Example city = "Denver"
Form 4 expression operator ALL (subquery)
Example (0.5 * target)< ALL(SELECT sales FROM salesreps WHERE repoffice = office)

With ALL, the column must meet the comparison condition for all values generated by the subquery before its record is included in the query results.

Form 5

expression operator ANY (subquery)

Example 0.1*quota)<ANY(SELECT amount FROM orders WHERE rep=EMPL_NUM)

With ANY, the expression must meet the comparison condition for at least one of the values generated by the subquery.

Form 6

expression [NOT] BETWEEN startrange AND endrange

Example orderdate BETWEEN "1990-01-01" AND "1990-06-30"

Checks to see if expression is within a range of values.

Form 7

[NOT] EXISTS (subquery)

Example EXISTS ;(SELECT*FROM orders WHERE product= product_id AND mfr=mfr_id AND amount > 25000.0)

Checks to see if at least one row meets the criteria in the subquery.

With EXISTS, the search condition evaluates to true unless the subquery evaluates to the empty set.

Form 8

expression [NOT] IN valueset

Example repoffice NOT IN ("11", "13", "22")

With IN, expression must be part of a given set of values before its item is included in the query results.

Form 9

expression [NOT] IN (subquery)

Example repoffice IN (SELECT office FROM offices WHERE sales > target)

With this form, expression must be part of the set of values returned by the subquery before its item is included in the query results.

Form 10

column1 [NOT] LIKE expC

Example company LIKE "Smith% Corp."

This search condition searches for each column1 that matches the specified expC. The wild card characters can be used <%> (percent) and <_> (underscore) as part of expC. The <_> character represents a single unknown character in the string and the <%> represents a sequence of zero or more unknown characters in the string.

Form 11

column1 IS [NOT] NULL

Example repoffice IS NULL

This search condition searches for each column1 that is NULL or empty string.

If two tables are included in a query, and a search condition is not specified, every item in the first table will be joined with every item in the second table as long as the search conditions are met. This can produce sizable query results.

UNION select_statement (syntax part)

Combines final results of one SELECT statement with final results of another SELECT statement. By default, UNION checks the combined results and eliminates duplicate rows. Use ALL to prevent UNION from eliminating duplicate rows from the combined results.

The rules for using UNION are:

  • Do not use UNION to combine subqueries.

  • Both SELECT statements must output the same number of columns.

  • Each column in the query results of one SELECT statement must have the same data type as the corresponding column in the other SELECT statement.

  • Only the final SELECT statement can have an ORDER BY clause, and ORDER BY must refer to output columns by number. If ORDER BY is included, it affects the entire result.

ORDER BY order_item (syntax part)

Use the ORDER BY option to sort query results based on values in the column(s). Each order_item must correspond to a column in the query results and can be:

A column in a FROM table that is also a select_item in the main SELECT clause (not in a subquery).

A numeric expression indicating the location of the column in the result table. The left column is number one.

DESC orders query results in descending order according to the order_item(s).

ASC specifies ascending order for query results and is the default.

Column Headers in SQL Queries

If the select_item is a column from the SQL table, column headers in query results are the column_name.

If the select_item includes the AS name option, name displays as the column header.

If the select_item is an expression, the column header corresponds to that expression.

If the select_item includes a function, the column header is the function.


  • Display companies in the CUSTOMERS table (one column from one table) on the screen.

    SELECT company FROM customers
  • Display three columns from two tables, and join the two tables based on the MFR column equal to EMPL_NUM column. Use local aliases for both tables.

    SELECT,, b.title FROM offices a, salesreps b
    WHERE a.mgr = b.empl_num
  • Display rows with unique data in the specified fields.

    SELECT DISTINCT,, b.title FROM offices a,
    salesreps b WHERE a.mgr = b.empl_num
  • Display rows in ascending order by CITY, NAMEE and TITLE.

    SELECT city, name, title FROM offices, salesreps
    WHERE mgr = empl_num ORDER BY CITY, NAME, TITLE
  • The INTO TABLE clause specifies that the results of the SELECT statement be inserted into the specified table.

    SELECT city, name, title FROM offices, salesreps
    WHERE mgr = empl_num INTO TABLE tempfile
  • Display all descriptions from the PRODUCTS table with a PRODUCT_ID and MFR_ID those match a PRODUCT and MFR in the orders table with amount of an order more than 25000.00.

    SELECT description FROM products a WHERE EXISTS
    (SELECT * FROM orders b WHERE a.product_id = b.product
    AND a.mfr_id = b.mfr and amount > 25000.00)
  • Display all records from the CUSTOMERS table having a company name that begins with a substring 'Smith' and finishes with a substring 'Corp.'

    SELECT company FROM customers a WHERE LIKE 'Smith% Corp.'

Join Operations

There are two types of joins commonly used to relate data from more that one table, the inner join and the outer join. The inner join returns only rows that meet the specified criteria, while the outer join maintains some or all of the unmatched data.

Using D3 SQL, an inner join can be accomplished using the SELECT statement involving two or more tables and a WHERE clause that specifies the join criteria. For instance, to display customers and their orders, a SELECT statement would include both the CUSTOMERS and ORDERS tables, using a column that is common to both tables, CUSTOMERID, to join the tables. For example,

SELECT customername, orderid, orderdate FROM customers, orders
WHERE customers.customerid = orders.customerid

In this query, only the customers with orders are returned.

If a list of all customers and their orders (if any) is desired, an outer join is needed. An outer join is simulated in D3 SQL using the UNION operator. The queries that will be "unioned" involve all the customers with orders and all the customers without orders. For example,

SELECT customername, orderid, orderdate FROM customers, orders 
WHERE customers.customerid = orders.customerid UNION SELECT customername, 
NULL, NULL FROM customers WHERE customerid NOT IN (select customerid FROM orders)

The first part of the UNION statement returns only customers with orders, while the second part of the UNION statement returns only customers not found in the orders table. Therefore, the UNION of the two queries includes all the customers. Since UNION statements must have the same number and type of columns, NULL is used as a placeholder.