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 theFROM
list is a real or virtual table. If you specify more than one element in theFROM
list, the specified elements are cross-joined. For more information, see theFROM
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 theWHERE
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 theHAVING
clause, groups that do not satisfy the specified condition are eliminated from the output. For more information, see theGROUP BY
clause andHAVING
clause topics. - You can use the
UNION
,INTERSECT
, andMINUS
operators to combine the output of more than oneSELECT
statement to form a single result set. TheUNION
operator returns all rows that are in one or both of the result sets. TheINTERSECT
operator returns all rows that are in both of the result sets. TheMINUS
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 specifyALL
in theUNION
operator, duplicate rows are not eliminated. For more information, see theUNION
clause,INTERSECT
clause, andMINUS
clause topics. - The actual output rows are computed using the
SELECT
output expressions for each selected row. For more information, see theSELECT
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 theCONNECT BY
clause topic. - If you specify the
ORDER BY
clause, the returned rows are sorted in the specified order. If you do not specify theORDER BY
clause, the rows are returned in whatever order the system finds fastest to produce. For more information, see theORDER 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 theDISTINCT
clause topic.- The
FOR UPDATE
clause causes theSELECT
statement to lock the selected rows against concurrent updates. For more information, see theFOR 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. |