When a query is created a table can be referenced in different ways, sometimes by its name or by a select query or a join result. Here is the syntax to refer a table.
|
table_reference: simple_table
| '(' select_query ')' [AS] table_name [ '(' column_name ',' ... ')' ]
| '(' select_query ')'
| joined_table | '(' joined_table ')' [AS] table_name [ '(' column_name ',' ... ')' ]
joined_table: '(' joined_table ')'
| table_reference { CROSS | NATURAL } JOIN table_reference
| table_reference { JOIN | UNIONJOIN} table_reference join_spec
| table_reference join_type JOIN table_reference join_spec
| table_reference NATURAL join_type JOIN table_reference
join_spec : ON search_condition | USING '(colunm_name ',' ...' )'
join_type: INNER | { LEFT | RIGHT | FULL } [OUTER]
|
The SQL framework for table expressions is based on the select-from-where construct.
|
select_query:
SELECT [ ALL | DISTINCT ] ( '*' | column_name ',' ... ) [ INTO column_name ',' ... ]
[ FROM table_name ',' ... ]
[ WHERE search_condition ]
[ GROUP BY column_name ',' ... ]
[ HAVING search_condition]
[ ORDER BY ordering_spec ',' ... ]
[ LIMIT posint ] [ OFFSET posint ]
|
The with clause provides the mechanism to introduce in-line view definitions:
|
with_clause : WITH with_element ',' ... select_query
with_element: table_name '(' column_name ',' ... ')' AS '(' select_query ')'
|
The pattern matching operations are used over predicates. It is possible to compare them, see the differences between them, if a predicate is a sub-predicate of another, etc. The following syntax description cover all the operations supported by MonetDB.
|
predicate:
comparison_predicate
| between_predicate
| like_predicate
| test_for_null
| in_predicate
| all_or_any_predicate
| existence_test
| '(' predicate ')'
comparison_predicate:
pred_expr COMPARISON pred_expr
| pred_expr = pred_exp between_predicate:
pred_expr [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] pred_expr AND pred_expr like_predicate:
pred_expr [NOT] LIKE atom_expr
test_for_null:
column_name IS [NOT] NULL
in_predicate:
pred_expr [NOT] IN '(' element_name ',' ... ')' all_or_any_predicate :
pred_expr COMPARISON { ANY | ALL | SOME } subquery
existence_test:
[NOT] EXISTS subquery pred_expr:
scalar_expr
| predicate
atom_expr:
['('] atom [')']
| ['('] atom [')'] ESCAPE string
| '?'
|
|
string_funcs:
SUBSTRING ( scalar_expr FROM scalar_expr FOR scalar_expr ')'
| SUBSTRING '(' scalar_expr ',' scalar_expr ',' scalar_expr ')'
| SUBSTRING '(' scalar_exprFROM scalar_expr ')'
| SUBSTRING '(' scalar_expr ',' scalar_expr ')'
| SUBSTRING '(' scalar_expr int_value scalar_expr ')'
| scalar_expr '||' scalar_expr
|
