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

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 ] ]

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

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

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

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

The "WINDOW window_definition_list" option and "SEED integer" option are supported from release Apr2019 (11.33.3) onwards.