SELECT¶

语法概要¶

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

其中 from_item 为以下之一：

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 [, ...] ) ]

并且 join_type 为以下之一：

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

并且 grouping_element 为以下之一：

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

说明¶

从零个或多个表中检索行。

SELECT ALL 保留重复行。SELECT DISTINCT 会从最终选择列表中移除重复行。

WITH 子句¶

WITH 子句定义可在查询中使用的命名关系。可以声明多个关系，后面的 WITH 关系可以引用前面的关系。

例如:

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;

可以在 WITH 关系上声明列别名:

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

不支持 WITH RECURSIVE。

GROUP BY 子句¶

GROUP BY 子句在计算聚合函数之前将行划分为多个分组。分组键可以是表达式、输入列，或选择列表中的序号位置。

例如，以下查询按同一个输出列分组:

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

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

使用 GROUP BY 子句时，输出表达式必须是聚合函数，或对每个分组保持常量的表达式。HAVING 会在分组和聚合求值后过滤分组:

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

复杂分组¶

对于已验证的分组键列，支持 GROUPING SETS、ROLLUP 和 CUBE。

示例:

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;

GROUPING 操作返回转换为整数的位集合，其中位值 1 表示对应参数未出现在当前分组集中:

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

集合运算¶

UNION、INTERSECT 和 EXCEPT 用于组合兼容的查询结果。

UNION 默认移除重复行。UNION ALL 保留重复行:

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 使用去重集合语义，返回两个输入中都存在的行:

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

EXCEPT 使用去重集合语义，返回第一个输入中存在但第二个输入中不存在的行:

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 和 EXCEPT ALL 的多重集语义记录为受支持。

ORDER BY 子句¶

ORDER BY 按一个或多个表达式对结果集排序。排序键可以是普通表达式，也可以是选择列表中的序号位置。

示例:

SELECT id, amount FROM orders ORDER BY amount DESC;

SELECT id, amount FROM orders ORDER BY 2 DESC;

可以显式指定 null 排序:

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

OFFSET 和 LIMIT 子句¶

OFFSET 丢弃开头的行。LIMIT 限制返回行数。如果两者同时存在，先应用 OFFSET，再应用 LIMIT:

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

LIMIT ALL 等价于省略限制:

SELECT id FROM orders ORDER BY id LIMIT ALL;

连接¶

连接用于组合多个关系中的行。

内连接可以使用 ON 条件:

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

支持 LEFT、RIGHT 和 FULL 外连接:

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 返回笛卡尔积。也可以通过在 FROM 子句中列出多个关系来表达:

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;

当两个被连接的关系暴露相同列名时，除非 USING 投影出合并后的连接键，否则应使用关系别名限定引用。

子查询¶

常见谓词形式中支持子查询。

EXISTS 检查子查询是否至少返回一行:

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

IN 检查值是否属于单列子查询结果:

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

在需要单个值的位置可以使用标量子查询:

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

不支持的 SELECT 特性¶

以下 SELECT 特性未记录为受支持：

WITH RECURSIVE
TABLESAMPLE 采样语义
UNNEST 和 WITH ORDINALITY
LATERAL 连接
INTERSECT ALL 和 EXCEPT ALL 多重集语义