Table Expressions

SELECT query

The SQL framework for table expressions is based on the select_query construct.

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

opt_distinct:
      ALL
    | DISTINCT

set_distinct:
      DISTINCT
    | ALL

When opt_distinct is not specified, ALL is assumed.
When set_distinct is not specified, DISTINCT is assumed.

Note: It is also possible to select data without a FROM-clause, such as:

SELECT CURRENT_DATE, (1+2) * 3, pi();

WITH clause

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 ')'

Recursive CTEs are not supported.

FROM clause

The FROM clause specifies the source(s) of the data on which the remainder of the query should operate. Logically, the FROM clause is where the query starts execution.

The FROM clause can contain a single table, a combination of multiple tables that are joined together using JOIN clauses, a (csv) file name, a table producing function, or another SELECT query inside a subquery node.

Here is the syntax to refer to a table.

table_reference:
      simple_table [ table_alias ]
    | joined_table [ table_alias ]
    | file_name_string [ table_alias ]
    | func_ref [ table_alias ]
    | LATERAL func_ref [ table_alias ]
    | '(' select_query ')' table_alias
    | LATERAL '(' select_query ')' table_alias
    | '(' VALUES '(' tuple ')'  [, '(' tuple ')' [, '('...')' ] ]  ')' table_alias

simple_table:
    [ schema_name '.' ] table_name

table_alias:
    [ AS ] alias [ '(' column_name [',' ...] ')' ]

joined_table:
      '(' joined_table ')'
    | table_reference CROSS JOIN table_reference
    | table_reference [ join_type ] JOIN table_reference join_spec
    | table_reference NATURAL [ join_type ] JOIN table_reference
    | '{' 'oj' table_reference outer_join_type OUTER JOIN table_reference join_spec '}'

join_type:
      INNER
    | outer_join_type [ OUTER ]

outer_join_type:
      LEFT
    | RIGHT
    | FULL

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

When no join_type is specified, INNER is assumed.

The LATERAL keyword allows subqueries in the FROM clause to refer to preceding table references. This feature is also known as a lateral join. Lateral joins are a generalization of correlated subqueries.

Table producing functions appearing in FROM can also be preceded by the key word LATERAL; the function's arguments can contain references to columns provided by preceding FROM items in any case.

GROUP BY clause

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;

Examples

-- query to list all non-system tables:
SELECT tbl.id, table_type_name, sch.name AS "Schema", tbl.name as "Table"
    , (select count(*) from sys.columns where table_id = tbl.id) as nr_of_columns
  FROM sys."tables" as tbl
 INNER JOIN sys.table_types ON tbl.type = table_type_id
 INNER JOIN sys."schemas" as sch ON tbl.schema_id = sch.id
 WHERE NOT tbl.system	-- exclude system tables and views
   AND table_type_name <> 'VIEW'	-- exclude user views
 ORDER BY "Schema", "Table";

-- example using window functions
SELECT id, emp_name, dep_name
     , ROW_NUMBER(FIX)           OVER (PARTITION BY dep_name ORDER BY id) AS row_number_in_frame
     , NTH_VALUE(emp_name, 2) OVER (PARTITION BY dep_name ORDER BY id) AS second_row_in_frame
     , LEAD(emp_name, 2)      OVER (PARTITION BY dep_name ORDER BY id) AS two_rows_ahead
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_over_1or2or3_rows
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_over_groups
     , SUM(salary)  OVER (PARTITION BY dep_name ORDER BY salary  RANGE BETWEEN 100.0 PRECEDING AND 50.0 FOLLOWING) AS sum_over_range
  FROM employee
 ORDER BY dep_name, id;

-- example using a table producing function
select * from generate_series(1,5);
select value as month_2021 from sys.generate_series({d'2021-01-01'}, {d'2022-01-01'}, interval '1' month);
select * from var() where name = 'debug';
SELECT * FROM file_loader('/tmp/csvfiles/new_data.csv');

-- example using csv_file name
SELECT * FROM '/tmp/csvfiles/new_data.csv';

-- example using distinct, subquery and unions
select DISTINCT word FROM ( select 'Abc' UNION ALL select 'Def' UNION ALL select 'Nop' ) AS words(word);

-- example producing cartesian product (all combinations of tuples of t1 with all tuples of t2)
SELECT t1.*, t2.* FROM t1 CROSS JOIN t2;
SELECT t1.*, t2.* FROM t1 , t2;

-- lateral join example
SELECT * FROM generate_series(1,4) t1(c1), LATERAL (SELECT c1 + 10) t2(c2);

-- example using ( VALUES (tuple),(tuple),... )
select *
 from ( values(0,'cero'),(1,'uno'),(2,'dos'),(3,'tres'),(4,'cuatro'),(5,'cinco'),(6,'seis'),(7,'siete'),(8,'ocho'),(9,'nueve'),(10,'diez') ) as nr_es(nr, nm);