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_expr ',' ... ) [ INTO column_expr ',' ... ]
    [ FROM table_name ',' ... ]
    [ WHERE search_condition ]
    [ GROUP BY column_expr ',' ... ]
    [ HAVING search_condition ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select_query ]
    [ ORDER BY ordering_spec ',' ... ]
    [ LIMIT posint ]
    [ OFFSET posint ]
    [ SAMPLE posint ]

Since Apr2019 release, expressions are allowed in the GROUP BY clause. The same expressions can be used in the projection clause, if an only if, they are literally equal (e.g. SELECT count(*)*(col1+col2) FROM t1 GROUP BY col1+col2).

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