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 variable_ref [',' ...] ]
    [ FROM table_reference [',' ...] ]
    [ WHERE search_condition ]
    [ GROUP BY { ALL | '*' | group_by_element [',' ...] } ]
    [ HAVING search_condition ]
    [ WINDOW window_definition_list ]
    [ QUALIFY search_condition ]
    [ { UNION | INTERSECT | EXCEPT | OUTER UNION } [ set_distinct ]
      [ CORRESPONDING [ BY '(' column_ref_commalist ')' ] ] select_clause ]
    [ ORDER BY ordering_spec [',' ...] ]
    [ limit_offset_clause | offset_fetchfirst_clause ]
    [ SAMPLE poslng_or_perc ]
    [ 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 [ RECURSIVE ] cte_element [',' cte_element [',' ...] ]

cte_element:
    query_alias [ '(' column_name [',' ...] ')' ] AS '(' select_query ')'

Note: Recursive CTEs are supported since Mar2025 (v11.53).

Example listing the fibonacci sequence numbers using RECURSIVE

WITH RECURSIVE fibonacci(position, current_value, next_value) AS
(
  -- Base case: start with position: 1, current_value: 0, and next_value: 1
  SELECT 1 as position, cast(0 as bigint) as current_value, cast(1 as bigint) as next_value
 UNION ALL
  -- Recursive case: Loop through the fibonacci sequence, ending at position 92.
  -- Putting the WHERE clause prevents runaway recursion (infinite loop).
  SELECT position + 1, next_value, current_value + next_value
    FROM fibonacci -- we call "ourself", creating recursion
   WHERE position <= 92
)
-- Select the results
SELECT position, current_value as fibonacci_number
  FROM fibonacci;

Recursive queries shine when querying for hierarchical data in a tree or graph-like data structure. These data structures generally don't have a predefined length, and we need to know how deep we need to query ahead of time. On the flip side, using recursive queries can add a performance penalty and can be easily prone to overflow or out-of-memory issues due to logic errors in setting up recursive queries or when the dataset is huge.

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 NATURAL [ join_type ] JOIN table_reference
    | table_reference [ join_type ] JOIN table_reference join_spec
    | '{' 'oj' table_reference [ join_type ] 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 and HAVING clauses. 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
   HAVING col1 + col2 > 3
       OR count(*) > 2;

SAMPLE clause

See SAMPLE for details.

Examples

-- query to list all user tables:
SELECT tbl.id, table_type_name as "Type", 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 like '%TABLE'	-- include only table types
 ORDER BY "Schema", "Table";

-- example using window functions
SELECT id, emp_name, dep_name
     , ROW_NUMBER()           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;

-- examples 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 sys.var() where name = 'debug';

-- examples reading external csv file data (use absolute paths)
SELECT * FROM file_loader('/tmp/csvfiles/movies.csv');
SELECT * FROM '/tmp/csvfiles/movies.csv';

-- 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)
 order by nm;

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