| 1 |
---------------------------------------- |
| 2 |
Select statements and select expressions |
| 3 |
---------------------------------------- |
| 4 |
|
| 5 |
Semantics: |
| 6 |
|
| 7 |
A select statement is used to return data to the caller (PSQL module or the |
| 8 |
client program). Select expressions retrieve parts of data that construct both |
| 9 |
the final the result set or any of the intermediate sets. Select expressions |
| 10 |
are also known as subqueries. |
| 11 |
|
| 12 |
Syntax rules: |
| 13 |
|
| 14 |
<select statement> ::= |
| 15 |
<select expression> [FOR UPDATE] [WITH LOCK] |
| 16 |
|
| 17 |
<select expression> ::= |
| 18 |
<query specification> [UNION [{ALL | DISTINCT}] <query specification>] |
| 19 |
|
| 20 |
<query specification> ::= |
| 21 |
SELECT [FIRST <value>] [SKIP <value>] <select list> |
| 22 |
FROM <table expression list> |
| 23 |
WHERE <search condition> |
| 24 |
GROUP BY <group value list> |
| 25 |
HAVING <group condition> |
| 26 |
PLAN <plan item list> |
| 27 |
ORDER BY <sort value list> |
| 28 |
ROWS <value> [TO <value>] |
| 29 |
|
| 30 |
<table expression> ::= |
| 31 |
<table name> | <joined table> | <derived table> |
| 32 |
|
| 33 |
<joined table> ::= |
| 34 |
{<cross join> | <qualified join>} |
| 35 |
|
| 36 |
<cross join> ::= |
| 37 |
<table expression> CROSS JOIN <table expression> |
| 38 |
|
| 39 |
<qualified join> ::= |
| 40 |
<table expression> [{INNER | {LEFT | RIGHT | FULL} [OUTER]}] JOIN <table expression> |
| 41 |
ON <join condition> |
| 42 |
|
| 43 |
<derived table> ::= |
| 44 |
'(' <select expression> ')' |
| 45 |
|
| 46 |
Conclusions: |
| 47 |
|
| 48 |
* FOR UPDATE mode and row locking can only be performed for a final dataset, |
| 49 |
they cannot be applied to a subquery |
| 50 |
* Unions are allowed inside any subquery |
| 51 |
* Clauses FIRST, SKIP, PLAN, ORDER BY, ROWS are allowed for any subquery |
| 52 |
|
| 53 |
Notes: |
| 54 |
|
| 55 |
* Either FIRST/SKIP or ROWS is allowed, they cannot be mixed together |
| 56 |
(a syntax error is thrown). |
| 57 |
|
| 58 |
* INSERT statement accepts a select expression to create a dataset to be |
| 59 |
inserted into a table. So its SELECT part supports all the features defined |
| 60 |
above. |
| 61 |
|
| 62 |
* UPDATE and DELETE statements are always based on an implicit cursor |
| 63 |
iterating through its target table and limited with the WHERE clause. You |
| 64 |
may also specify the final parts of the select expression syntax to limit |
| 65 |
the number of affected rows or optimize the statement. The following clauses |
| 66 |
are allowed at the end of the UPDATE/DELETE statements: PLAN, ORDER BY and |
| 67 |
ROWS. |