Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.54 SELECT


SELECT -- retrieve rows from a table or view


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 [, ...] [ LEVEL ] ]
  [ HAVING condition [, ...] ]
  [ { UNION [ ALL ] | INTERSECT | MINUS } select ]
  [ ORDER BY expression [ ASC | DESC ] [, ...] ]

where from_item can be one of:

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


SELECT retrieves rows from one or more tables. The general processing of SELECT is as follows:

    1. All elements in the FROM list are computed. (Each element in the FROM list is a real or virtual table.) If more than one element is specified in the FROM list, they are cross-joined together. (See FROM clause, below.)

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

    3. If the GROUP BY clause is specified, the output is divided into groups of rows that match on one or more values. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition. (See GROUP BY clause and HAVING clause below.)

    4. Using the operators UNION, INTERSECT, and MINUS, the output of more than one SELECT statement can be combined 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 strictly in both result sets. The MINUS operator returns the rows that are in the first result set but not in the second. In all three cases, duplicate rows are eliminated. In the case of the UNION operator, if ALL is specified then duplicates are not eliminated. (See UNION clause, INTERSECT clause, and MINUS clause below.)

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

    6. The CONNECT BY clause is used to select data that has a hierarchical relationship. Such data has a parent-child relationship between rows. (See CONNECT BY clause.)

    7. 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 clause below.)

    8. DISTINCT eliminates duplicate rows from the result. ALL (the default) will return all candidate rows, including duplicates. (See DISTINCT clause below.)

    9. The FOR UPDATE clause causes the SELECT statement to lock the selected rows against concurrent updates. (See FOR UPDATE clause below.)

You must have SELECT privilege on a table to read its values. The use of FOR UPDATE requires UPDATE privilege as well.



Comment-embedded hints to the optimizer for selection of an execution plan. See Section 3.4 for information on optimizer hints.

The remaining parameters are discussed within the following sections.

Previous PageTable Of ContentsNext Page