Table expressions

Table expressions mk Sat, 03/27/2010 - 22:45

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 to a table.

table_reference:
      simple_table
    | joined_table [ [AS] alias [ '(' column_name [',' ...] ')' ] ]
    | '(' select_query ')' [ [AS] alias [ '(' column_name [',' ...] ')' ] ]
    | '(' VALUES '(' tuple ')' [, '(' tuple ')' [, '('...')' ] ] ')' [ [AS] alias [ '(' column_name [',' ...] ')' ] ]

joined_table:
      '(' joined_table ')'
    | table_reference CROSS JOIN table_reference
    | table_reference NATURAL [ join_type ] JOIN table_reference
    | table_reference [ join_type ] JOIN table_reference join_spec
    | table_reference UNIONJOIN table_reference join_spec

join_type:
      INNER
    | { LEFT | RIGHT | FULL } [ OUTER ]

join_spec:
      ON search_condition
    | USING '(' colunm_name [',' ...] ')'

When no join_type is specified, INNER is assumed.

The SQL framework for table expressions is based on the select-from-where construct.

select_query:
    [ with_clause ]
    SELECT [ ALL | DISTINCT ]   ( '*' | column_expr [',' ...] )
    [ INTO column_expr [',' ...] ]
    [ FROM table_reference [',' ... ] ]
    [ WINDOW window_definition_list ]
    [ WHERE search_condition ]
    [ GROUP BY column_expr [',' ...] ]
    [ HAVING search_condition ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING [ BY '(' column_ref_commalist ')' ] ] select_query ]
    [ ORDER BY ordering_spec [',' ...] ]
    [ LIMIT posint ]
    [ OFFSET posint ]
    [ SAMPLE posint [ SEED integer ] ]

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

The WITH clause prefix (aka Common Table Expressions (CTE)) provides the mechanism to introduce temporary in-line view definitions:

with_clause:
    WITH cte_element [',' cte_element [',' ...] ]

cte_element:
    query_alias [ '(' 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_expr

between_predicate:
     pred_expr [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] pred_expr AND pred_expr

like_predicate:
     pred_expr [NOT] { LIKE | ILIKE } 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 [ ESCAPE string ]
     | '(' atom ')' [ ESCAPE string ]
     | '(' atom_expr ')'
     | '?'

string_funcs:
      scalar_expr '||' scalar_expr
    | SUBSTRING '(' scalar_expr FROM scalar_expr [ FOR scalar_expr ] ')'
    | SUBSTRING '(' scalar_expr ',' scalar_expr [ ',' scalar_expr ] ')'
    | SUBSTRING '(' scalar_expr int_value scalar_expr ')'