SELECT¶

Synopsis¶

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ OFFSET count [ { ROW | ROWS } ] ]
[ LIMIT { count | ALL } ]

where from_item is one of:

table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( query ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( VALUES expression [, ...] ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and join_type is one of:

[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN

and grouping_element is one of:

()
expression
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )

Description¶

Retrieve rows from zero or more tables.

SELECT ALL keeps duplicate rows. SELECT DISTINCT removes duplicate rows from the final select list.

WITH Clause¶

The WITH clause defines named relations for use within a query. Multiple relations can be declared, and later WITH relations may refer to earlier ones.

For example:

WITH base AS (
    SELECT grp, amount FROM orders WHERE amount IS NOT NULL
),
totals AS (
    SELECT grp, SUM(amount) AS total_amount FROM base GROUP BY grp
)
SELECT grp, total_amount FROM totals ORDER BY grp;

Column aliases can be declared on a WITH relation:

WITH renamed (x, y) AS (SELECT id, amount FROM orders)
SELECT x, y FROM renamed;

WITH RECURSIVE is not supported.

GROUP BY Clause¶

The GROUP BY clause divides rows into groups before aggregate functions are computed. Grouping keys may be expressions, input columns, or ordinal positions from the select list.

For example, these queries group by the same output column:

SELECT grp, COUNT(*) FROM orders GROUP BY grp;

SELECT grp, COUNT(*) FROM orders GROUP BY 1;

When a GROUP BY clause is used, output expressions must be aggregate functions or expressions that are constant for each group. HAVING filters groups after grouping and aggregate evaluation:

SELECT grp, COUNT(*), SUM(amount)
FROM orders
GROUP BY grp
HAVING SUM(amount) >= 30
ORDER BY grp;

Complex Grouping¶

GROUPING SETS, ROLLUP and CUBE are supported for verified grouping key columns.

Examples:

SELECT COALESCE(grp, 'all') AS g, COUNT(*)
FROM orders
GROUP BY GROUPING SETS ((grp), ())
ORDER BY g;

SELECT COALESCE(grp, 'all') AS g, COUNT(*)
FROM orders
GROUP BY ROLLUP (grp)
ORDER BY g;

SELECT COALESCE(grp, 'all') AS g, COUNT(*)
FROM orders
GROUP BY CUBE (grp)
ORDER BY g;

The GROUPING operation returns a bit set converted to an integer, where a bit value of 1 means the corresponding argument is not present in the current grouping set:

SELECT COALESCE(grp, 'all') AS g, COUNT(*), GROUPING(grp)
FROM orders
GROUP BY GROUPING SETS ((grp), ())
ORDER BY g;

Set Operations¶

UNION, INTERSECT and EXCEPT combine compatible query results.

UNION removes duplicate rows by default. UNION ALL keeps duplicate rows:

SELECT x FROM (VALUES 1, 2, 2) AS t(x)
UNION
SELECT x FROM (VALUES 2, 3) AS u(x)
ORDER BY x;

SELECT x FROM (VALUES 1, 2, 2) AS t(x)
UNION ALL
SELECT x FROM (VALUES 2, 3) AS u(x)
ORDER BY x;

INTERSECT returns rows present in both inputs, using distinct set semantics:

SELECT x FROM (VALUES 1, 2, 2) AS t(x)
INTERSECT
SELECT x FROM (VALUES 2, 3) AS u(x)
ORDER BY x;

EXCEPT returns rows from the first input that are not present in the second input, using distinct set semantics:

SELECT x FROM (VALUES 1, 2, 2) AS t(x)
EXCEPT
SELECT x FROM (VALUES 2, 3) AS u(x)
ORDER BY x;

INTERSECT ALL and EXCEPT ALL multiset semantics are not documented as supported in this path.

ORDER BY Clause¶

ORDER BY sorts the result set by one or more expressions. Sort keys may be ordinary expressions or ordinal positions from the select list.

Examples:

SELECT id, amount FROM orders ORDER BY amount DESC;

SELECT id, amount FROM orders ORDER BY 2 DESC;

The null ordering can be specified explicitly:

SELECT x FROM (VALUES 2, NULL, 1) AS t(x) ORDER BY x NULLS FIRST;

OFFSET and LIMIT Clauses¶

OFFSET discards leading rows. LIMIT restricts the number of returned rows. If both are present, OFFSET is applied before LIMIT:

SELECT id FROM orders ORDER BY id OFFSET 1 LIMIT 2;

LIMIT ALL is equivalent to omitting the limit:

SELECT id FROM orders ORDER BY id LIMIT ALL;

Joins¶

Joins combine rows from multiple relations.

Inner joins can use an ON condition:

SELECT l.id, l.v, r.w
FROM left_table l
JOIN right_table r ON l.id = r.id;

LEFT, RIGHT and FULL outer joins are supported:

SELECT id, v, w
FROM left_table
LEFT JOIN right_table USING (id);

SELECT r.id, l.v, r.w
FROM left_table l
RIGHT JOIN right_table r ON l.id = r.id;

SELECT COALESCE(l.id, r.id), COALESCE(l.v, r.w)
FROM left_table l
FULL JOIN right_table r ON l.id = r.id;

CROSS JOIN returns the Cartesian product. It can also be written by listing multiple relations in the FROM clause:

SELECT l.id, r.id
FROM left_table l
CROSS JOIN right_table r;

SELECT l.id, r.id
FROM left_table l, right_table r;

When two joined relations expose the same column name, qualify the reference with a relation alias unless USING projects a merged join key.

Subqueries¶

Subqueries are supported in common predicate forms.

EXISTS checks whether a subquery returns at least one row:

SELECT id
FROM orders o
WHERE EXISTS (SELECT 1 FROM right_table r WHERE r.id = o.id);

IN checks membership in a single-column subquery:

SELECT id
FROM orders
WHERE id IN (SELECT id FROM right_table);

A scalar subquery can be used where a single value is expected:

SELECT id
FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);

Unsupported SELECT Features¶

The following SELECT features are not documented as supported:

• WITH RECURSIVE

• TABLESAMPLE sampling semantics

• UNNEST and WITH ORDINALITY

• LATERAL joins

• INTERSECT ALL and EXCEPT ALL multiset semantics