SELECT — retrieve rows from a table or view


SELECT  [ optimizer_hint ]
        [ ALL | DISTINCT ]
          { select_item [ [AS] output_name ] } [, ...]
           FROM from_item
         [ WHERE condition ]
         [ GROUP BY expression [, ...] [ HAVING condition [, ...] ] ]
         [ UNION [ ALL | DISTINCT ] select ]
         [ ORDER BY { order_list [ COLLATE collation_name ] [ ASC | DESC] } [, ...] ]
         [ LIMIT { count [ OFFSET start ] | start [ , count ] }
         [ OFFSET start [ ROW | ROWS ] [ FETCH {FIRST | NEXT} 
           count [ROW | ROWS] [ONLY] ] ]
         [ FETCH {FIRST | NEXT } count [ROW | ROWS] [ONLY] ]
         [ FOR UPDATE [NOWAIT] ]

Where select_item can be any one of the following:

* | table.*  |  view.*  |  alias.*
[[schema.]table.]column_name  |  [[schema.]view.]column_name  |  [alias.]column_name

Where from_item can be any one of the following:

[schema.]object_name [ [AS] alias ] [, ...]
( select ) [ [AS] alias ] [, ...]
from_item { join_type from_item 
    { ON join_condition [...] | USING (column_name [, ...]) } }
{ scalar_function | table_function }


Use a SELECT statement or subquery to retrieve data from one or more tables or views. An abstract version of the processing steps for a SELECT statement is as follows:

  1. The actual output rows are computed using the SELECT output expressions for each selected row (see SELECT list below).

  2. SELECT DISTINCT eliminates duplicate rows from the result set.

  3. SELECT ALL (the default) returns all candidate rows, including duplicates (see ALL|DISTINCT under SELECT list below).
  4. All elements in the FROM list are computed. (Each element in the FROM list is a real, derived, or virtual table (as in DUAL), or a view.) If more than one element is specified in the FROM list, separated by commas, they are joined together (see FROM list below).

  5. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output (see WHERE below).

  6. If the GROUP BY clause is specified, the output is aggregated into groups of rows that match on one or more values. If the HAVING clause is also present, NuoDB eliminates groups that do not satisfy the given condition (see GROUP BY below).

  7. The UNION operator combines the output of multiple SELECT statements to form a single result set. The UNION operator returns all rows that are in one or both of the result sets. Duplicate rows are eliminated unless UNION ALL is specified. Notice that UNION DISTINCT is the default here, as duplicate rows are eliminated, even though ALL is the default for the SELECT itself (see UNION below). NuoDB uses a common type algorithm (Data Type Conversion) to decide the type, precision and scale of each column in the result of a query with UNION branches and coerces the columns in each branch to the common type. The common type is computed from the top down through the branches and is not commutative.

  8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce (see ORDER BY below). Use the keyword COLLATE to change the character classification behavior of the sort order.

  9. If the LIMIT, OFFSET or FETCH clause is specified, the SELECT statement returns a subset of the result set (see LIMIT, FETCH, OFFSET below).

  10. The FOR UPDATE clause controls whether the transaction for the current session places a lock on the retrieved records (see FOR UPDATE below).

Results of queries to the transaction engine will be buffered back to the client in rows that fit into less than 100 KB. Once the client iterates through a set of fetched results, it will go back to the TE to get more results.

Note: You must have SELECT privilege on all tables and/or views to access them in a SELECT command. The use of FOR UPDATE requires UPDATE privilege on the table being updated. Views cannot be updated.

Note: The use of a column alias cannot be used in the WHERE, GROUP BY, or HAVING clause. For example, "SELECT field as F FROM table WHERE F > 0" is not supported. Allowing such a condition can result in ambiguous name resolution and therefore is not allowed.


Optimizer Hints

Optimizer hints are a means to indicate how the optimizer should evaluate the SELECT statement. For example, you may know, based on the data in a given column, that one index may be more selective than another for the particular query you are executing. An optimizer hint is useful in this situation. An optimizer hint is embedded in the SQL query, right after the SELECT keyword. It has the following format:

/*+ hint[,hint]... */

Where the following values (case-insensitive) are allowed to substitute for hint:

USE_INDEX ( [schema.]table [ , index_list ] ) SKIP_INDEX ( [schema.]table [ , index_list ] )

table is the name of a table or its alias, as specified in the AS alias clause of the SELECT statement. The table name may optionally be schema qualified. If a table has an alias then all index hints on that table must reference the alias.

index_list is a list of one or more index specifications separated by commas, where each index specification has the format:

[ ID_ORDERED ] index_name
[ KEY_ORDERED ] index_name

ID_ORDERED and KEY_ORDERED designate the scan type for the index. ID_ORDERED refers to a query that is reading data that corresponds to the range specified over a given index in ROWID order. KEY_ORDERED refers to a query that is reading data in index key order.

If you specify conflicting hints (for example, /*+ USE_INDEX(foo, foo_a), SKIP_INDEX(foo, foo_a) */), where foo is a table and foo_a is an index on a column in table foo, an error will be thrown.

If you specify an optimizer hint in a view or derived table, then the view or derived table will not be merged with the outer query results. For example, if you have the query:

SELECT * FROM (SELECT * FROM foo WHERE foo.a = 12);

In this case, we merge the plan for the inner select query (the derived table) with the plan for the outer select query, and then process the resulting query plan. If the inner select query contains hints as below, then the query blocks won't be merged.

SELECT * FROM (SELECT /*+ USE_INDEX(foo) */ * FROM foo WHERE foo.a = 12);

The difference between processing queries with or without merging is that they will have different query plans and may result in different run times.


The ORDERED hint tells the optimizer to join tables in the order that they are specified in the FROM clause.


FROM t1, t2, t4, t3 WHERE t1.col = t2.col AND t1.col = t3.col AND t1.col = t4.col;


USE_INDEX ( [schema.]table [ , index_list ] )

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The USE_INDEX hint tells the optimizer to use an index (or a set of indexes) for a table.

In the examples above, foo is a table and foo_a is an index on a column in table foo.

See Example 15 below.


SKIP_INDEX ( [schema.]table [ , index_list ] )

Note: You can use [schema.]table or you may need to use table alias. This is needed if you do a join between the same table more than once.

The SKIP_INDEX hint tells the optimizer to not use an index (or a set of indexes) for a table.

In the examples above, foo is a table and foo_a is an index on a column in table foo.

See Example 16 below.

Examples for SELECT

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.