Skip to main content

Table expressions

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