Retrieves rows from a table or view.

Syntax

SELECT [ optimizer_hint ] [ ALL | DISTINCT ]
  * | expression [ AS output_name ] [, ...]
  FROM from_item [, ...]
  [ WHERE condition ]
  [ [ START WITH start_expression ]
      CONNECT BY { PRIOR parent_expr = child_expr |
 child_expr = PRIOR parent_expr }
    [ ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...] ] ]
  [ GROUP BY { expression | ROLLUP ( expr_list ) |
      CUBE ( expr_list ) | GROUPING SETS ( expr_list ) } [, ...]
      [ LEVEL ] ]
  [ HAVING condition [, ...] ]
  [ { UNION [ ALL ] | INTERSECT | MINUS } select ]
  [ ORDER BY expression [ ASC | DESC ] [, ...] ]
  [ FOR UPDATE [WAIT n|NOWAIT|SKIP LOCKED]]

The following options for from_item are available:

 table_name[@dblink ] [ alias ]
  ( select ) alias
 from_item [ NATURAL ] join_type from_item
    [ ON join_condition | USING ( join_column [, ...] ) ]

Description

You can use the SELECT statement to retrieve rows from one or more tables. The general processing of SELECT is described as follows:

  • All elements in the FROM list are computed. Each element in the FROM list is a real or virtual table. If you specify more than one element in the FROM list, the specified elements are cross-joined. For more information, see the FROM clause topic.
  • If you specify the WHERE clause, all rows that do not satisfy the condition are eliminated from the output. For more information, see the WHERE clause topic.
  • If you specify the GROUP BY clause, the output is divided into groups of rows that match on one or more values. If you specify the HAVING clause, groups that do not satisfy the specified condition are eliminated from the output. For more information, see the GROUP BY clause and HAVING clause topics.
  • You can use the UNION, INTERSECT, and MINUS operators to combine the output of more than one SELECT statement to form a single result set. The UNION operator returns all rows that are in one or both of the result sets. The INTERSECT operator returns all rows that are in both of the result sets. The MINUS operator returns the rows that are in the first result set but not in the second result set. In all the preceding three cases, duplicate rows are eliminated. If you specify ALL in the UNION operator, duplicate rows are not eliminated. For more information, see the UNION clause, INTERSECT clause, and MINUS clause topics.
  • The actual output rows are computed using the SELECT output expressions for each selected row. For more information, see the SELECT list topic.
  • The CONNECT BY clause is used to select data that has a hierarchical relationship. This type of data has a parent-child relationship between rows. For more information, see the CONNECT BY clause topic.
  • If you specify the ORDER BY clause, the returned rows are sorted in the specified order. If you do not specify the ORDER BY clause, the rows are returned in whatever order the system finds fastest to produce. For more information, see the ORDER BY clause topic.
  • DISTINCT eliminates duplicate rows from the result. ALL returns all candidate rows, including duplicate rows. The default value is ALL. For more information, see the DISTINCT clause topic.
  • The FOR UPDATE clause causes the SELECT statement to lock the selected rows against concurrent updates. For more information, see the FOR UPDATE clause topic.

You must have the SELECT privilege on a table to read its values. To use the FOR UPDATE statement, you must have the UPDATE privilege.

Parameters

Parameter Description
optimizer_hint Comment-embedded hints to the optimizer. This parameter is used to select an execution plan.