FROM 和 JOIN 子句 – DuckDB

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 查询语法

FROM 和 JOIN 子句

FROM 子句指定了查询后续操作所依赖的数据源。从逻辑上讲，查询是从 FROM 子句开始执行的。FROM 子句可以包含单个表、通过 JOIN 子句连接的多个表的组合，或者是子查询节点内的另一个 SELECT 查询。DuckDB 还提供了一种可选的 FROM-first（FROM 优先）语法，使您无需 SELECT 语句即可进行查询。

示例

从名为 tbl 的表中选择所有列

SELECT *
FROM tbl;

使用 FROM-first 语法从表中选择所有列

FROM tbl
SELECT *;

使用 FROM-first 语法并省略 SELECT 子句来选择所有列

FROM tbl;

通过别名 tn 从名为 tbl 的表中选择所有列

SELECT tn.*
FROM tbl tn;

使用前缀别名

SELECT tn.*
FROM tn: tbl;

从模式（schema）schema_name 中的表 tbl 选择所有列

SELECT *
FROM schema_name.tbl;

从表函数 range 中选择列 i，其中 range 函数的第一列被重命名为 i

SELECT t.i
FROM range(100) AS t(i);

从名为 test.csv 的 CSV 文件中选择所有列

SELECT *
FROM 'test.csv';

从子查询中选择所有列

SELECT *
FROM (SELECT * FROM tbl);

将表的整行作为结构体（struct）选择

SELECT t
FROM t;

将子查询的整行作为结构体选择（即单列）

SELECT t
FROM (SELECT unnest(generate_series(41, 43)) AS x, 'hello' AS y) t;

连接两个表

SELECT *
FROM tbl
JOIN other_table
  ON tbl.key = other_table.key;

从表中选择 10% 的样本

SELECT *
FROM tbl
TABLESAMPLE 10%;

从表中选择 10 行样本

SELECT *
FROM tbl
TABLESAMPLE 10 ROWS;

在 WHERE 子句和聚合中使用 FROM-first 语法

FROM range(100) AS t(i)
SELECT sum(t.i)
WHERE i % 2 = 0;

表函数

DuckDB 中的某些函数返回的是整个表而不是单个值。这些函数相应地被称为表函数，可以像普通表引用一样与 FROM 子句一起使用。示例包括 read_csv、read_parquet、range、generate_series、repeat、unnest 和 glob（注意，此处的一些示例既可以用作标量函数，也可以用作表函数）。

例如，

SELECT *
FROM 'test.csv';

被隐式转换为对 read_csv 表函数的调用

SELECT *
FROM read_csv('test.csv');

所有表函数都支持 WITH ORDINALITY 后缀，它通过一个名为 ordinality 的整数列扩展返回的表，该列从 1 开始枚举生成的行。

SELECT * 
FROM read_csv('test.csv') WITH ORDINALITY;

请注意，使用 row_number 窗口函数也可以达到同样的结果。然而，在存在连接的情况下，WITH ORDINALITY 允许在不诉诸子查询的情况下，对连接的一侧进行枚举，而不是对最终结果集进行枚举。

连接 (Joins)

连接是一种基本的关联操作，用于水平连接两个表或关系。根据在连接子句中的书写顺序，这些关系被称为连接的左侧和右侧。每个结果行都包含来自两个关系的列。

连接使用某种规则来匹配来自每个关系的行对。通常这是一个谓词，但也可以指定其他隐含规则。

外连接 (Outer Joins)

如果指定了 OUTER 连接，即使没有匹配项的行也可以被返回。外连接可以是以下之一：

LEFT（左侧关系中的所有行至少出现一次）
RIGHT（右侧关系中的所有行至少出现一次）
FULL（两个关系中的所有行至少出现一次）

非 OUTER 的连接称为 INNER 连接（仅返回匹配到的行）。

当返回未匹配的行时，来自另一个表的属性被设置为 NULL。

交叉连接 (笛卡尔积)

最简单的连接类型是 CROSS JOIN。这种类型的连接没有条件，它只是返回所有可能的对。

返回所有行对

SELECT a.*, b.*
FROM a
CROSS JOIN b;

这等同于省略 JOIN 子句

SELECT a.*, b.*
FROM a, b;

条件连接

大多数连接都是由一个将一侧的属性连接到另一侧属性的谓词指定的。条件可以使用连接的 ON 子句明确指定（更清晰），也可以由 WHERE 子句隐含指定（传统方式）。

我们使用 TPC-H 模式中的 l_regions 和 l_nations 表

CREATE TABLE l_regions (
    r_regionkey INTEGER NOT NULL PRIMARY KEY,
    r_name      CHAR(25) NOT NULL,
    r_comment   VARCHAR(152)
);

CREATE TABLE l_nations (
    n_nationkey INTEGER NOT NULL PRIMARY KEY,
    n_name      CHAR(25) NOT NULL,
    n_regionkey INTEGER NOT NULL,
    n_comment   VARCHAR(152),
    FOREIGN KEY (n_regionkey) REFERENCES l_regions(r_regionkey)
);

返回国家/地区对应的区域

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r ON (n_regionkey = r_regionkey);

如果列名相同且要求相等，则可以使用更简单的 USING 语法

CREATE TABLE l_regions (regionkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        comment   VARCHAR(152));

CREATE TABLE l_nations (nationkey INTEGER NOT NULL PRIMARY KEY,
                        name      CHAR(25) NOT NULL,
                        regionkey INTEGER NOT NULL,
                        comment   VARCHAR(152),
                        FOREIGN KEY (regionkey) REFERENCES l_regions(regionkey));

返回国家/地区对应的区域

SELECT n.*, r.*
FROM l_nations n
JOIN l_regions r USING (regionkey);

表达式不一定是相等关系——可以使用任何谓词

返回工作耗时更长但成本更低的工作对

SELECT s1.t_id, s2.t_id
FROM west s1, west s2
WHERE s1.time > s2.time
  AND s1.cost < s2.cost;

自然连接 (Natural Joins)

自然连接基于具有相同名称的属性连接两个表。

例如，看下面关于城市、机场代码和机场名称的例子。请注意，两个表都是故意不完整的，即它们在另一个表中没有匹配对。

CREATE TABLE city_airport (city_name VARCHAR, iata VARCHAR);
CREATE TABLE airport_names (iata VARCHAR, airport_name VARCHAR);
INSERT INTO city_airport VALUES
    ('Amsterdam', 'AMS'),
    ('Rotterdam', 'RTM'),
    ('Eindhoven', 'EIN'),
    ('Groningen', 'GRQ');
INSERT INTO airport_names VALUES
    ('AMS', 'Amsterdam Airport Schiphol'),
    ('RTM', 'Rotterdam The Hague Airport'),
    ('MST', 'Maastricht Aachen Airport');

要基于它们共享的 IATA 属性连接这两个表，请运行：

SELECT *
FROM city_airport
NATURAL JOIN airport_names;

这将产生以下结果：

city_name	iata	airport_name
Amsterdam	AMS	阿姆斯特丹史基浦机场
鹿特丹	RTM	鹿特丹海牙机场

请注意，结果中仅包含了两个表中都存在相同 iata 属性的行。

我们也可以使用带有 USING 关键字的普通 JOIN 子句来表达此查询

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

半连接 (Semi Joins) 和反连接 (Anti Joins)

半连接返回左表中在右表中至少有一个匹配项的行。反连接返回左表中在右表中没有匹配项的行。使用半连接或反连接时，结果中的行数永远不会超过左表。半连接提供的逻辑与 IN 运算符语句相同。反连接提供的逻辑与 NOT IN 运算符相同，不同之处在于反连接会忽略来自右表的 NULL 值。

半连接示例

从 city_airport 表中返回城市-机场代码对列表，其中机场名称在 airport_names 表中可用

SELECT *
FROM city_airport
SEMI JOIN airport_names
    USING (iata);

city_name	iata
Amsterdam	AMS
鹿特丹	RTM

此查询等同于

SELECT *
FROM city_airport
WHERE iata IN (SELECT iata FROM airport_names);

反连接示例

从 city_airport 表中返回城市-机场代码对列表，其中机场名称在 airport_names 表中不可用

SELECT *
FROM city_airport
ANTI JOIN airport_names
    USING (iata);

city_name	iata
埃因霍温	EIN
格罗宁根	GRQ

此查询等同于

SELECT *
FROM city_airport
WHERE iata NOT IN (SELECT iata FROM airport_names WHERE iata IS NOT NULL);

横向连接 (Lateral Joins)

LATERAL 关键字允许 FROM 子句中的子查询引用之前的子查询。此功能也称为横向连接。

SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

i	j
0	1
2	3
1	2

横向连接是相关子查询的泛化，因为它们可以为每个输入值返回多个值，而不是仅仅返回单个值。

SELECT *
FROM
    generate_series(0, 1) t(i),
    LATERAL (SELECT i + 10 UNION ALL SELECT i + 100) t2(j);

i	j
0	10
1	11
0	100
1	101

将 LATERAL 视为一个循环可能会有帮助，我们遍历第一个子查询的行，并将其用作第二个（LATERAL）子查询的输入。在上面的示例中，我们遍历表 t，并在表 t2 的定义中引用其列 i。t2 的行构成了结果中的列 j。

可以引用来自 LATERAL 子查询的多个属性。使用第一个示例中的表：

CREATE TABLE t1 AS
    SELECT *
    FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

SELECT *
    FROM t1, LATERAL (SELECT i + j) t2(k)
    ORDER BY ALL;

i	j	k
0	1	1
1	2	3
2	3	5

DuckDB 会检测何时应该使用 LATERAL 连接，使得 LATERAL 关键字的使用变为可选。

位置连接 (Positional Joins)

在使用相同大小的数据框或其他嵌入表时，行可能基于其物理顺序具有自然的对应关系。在脚本语言中，这可以很容易地通过循环来表达：

for (i = 0; i < n; i++) {
    f(t1.a[i], t2.b[i]);
}

在标准 SQL 中很难表达这一点，因为关系表不是有序的，但导入的表（如数据框）或磁盘文件（如 CSV 或 Parquet 文件）确实具有自然顺序。

使用此顺序连接它们称为位置连接：

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (s VARCHAR);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES ('a'), ('b');

SELECT *
FROM t1
POSITIONAL JOIN t2;

x	s
1	a
2	b
3	NULL

位置连接始终是 FULL OUTER 连接，即结果表的长度为较长输入表的长度，缺失条目用 NULL 值填充。

截止连接 (As-Of Joins)

处理时间序列或类似排序数据时的常见操作是查找参考表（如价格）中最近的（第一个）事件。这称为截止连接 (as-of join)：

将价格附加到股票交易上

SELECT t.*, p.price
FROM trades t
ASOF JOIN prices p
       ON t.symbol = p.symbol AND t.when >= p.when;

ASOF 连接要求排序字段上至少有一个不等式条件。不等式可以是任何数据类型上的任何不等式条件（>=, >, <=, <），但最常见的形式是时间类型上的 >=。任何其他条件必须是相等条件（或 NOT DISTINCT）。这意味着表的左/右顺序很重要。

ASOF 将每个左侧行与至多一个右侧行连接。它可以指定为 OUTER 连接，以查找未匹配的行（例如，没有价格的交易，或没有交易的价格）。

将价格或 NULL 附加到股票交易上

SELECT *
FROM trades t
ASOF LEFT JOIN prices p
            ON t.symbol = p.symbol
           AND t.when >= p.when;

ASOF 连接也可以使用 USING 语法指定匹配列名的连接条件，但列表中的最后一个属性必须是不等式，它将是大于或等于 (>=)

SELECT *
FROM trades t
ASOF JOIN prices p USING (symbol, "when");

返回 symbol, trades.when, price（但不是 prices.when）

如果您像这样将 USING 与 SELECT * 结合使用，查询将返回匹配项的左侧（探测）列值，而不是右侧（构建）列值。要在示例中获取 prices 时间，您需要明确列出这些列

SELECT t.symbol, t.when AS trade_when, p.when AS price_when, price
FROM trades t
ASOF LEFT JOIN prices p USING (symbol, "when");

自连接 (Self-Joins)

DuckDB 允许所有类型的连接进行自连接。请注意，表需要设置别名；使用不带别名的相同表名将导致错误。

CREATE TABLE t (x INTEGER);
SELECT * FROM t JOIN t USING(x);

Binder Error:
Duplicate alias "t" in query!

添加别名允许查询成功解析

SELECT * FROM t AS t1 JOIN t AS t2 USING(x);

`JOIN` 子句中的简写

您可以在 JOIN 子句中指定列名

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (y INTEGER);
INSERT INTO t1 VALUES (1), (2), (4);
INSERT INTO t2 VALUES (2), (3);
SELECT * FROM t1 NATURAL JOIN t2 t2(x);

x
2

您也可以在 JOIN 子句中使用 VALUES 子句

SELECT * FROM t1 NATURAL JOIN (VALUES (2), (4)) _(x);

x
2
4

`FROM`-First 语法

DuckDB 的 SQL 支持 FROM-first 语法，即它允许将 FROM 子句放在 SELECT 子句之前，或完全省略 SELECT 子句。我们使用以下示例进行演示：

CREATE TABLE tbl AS
    SELECT *
    FROM (VALUES ('a'), ('b')) t1(s), range(1, 3) t2(i);

带有 `SELECT` 子句的 `FROM`-first 语法

以下语句演示了 FROM-first 语法的使用

FROM tbl
SELECT i, s;

这等同于

SELECT i, s
FROM tbl;

i	s
1	a
2	a
1	b
2	b

没有 `SELECT` 子句的 `FROM`-first 语法

以下语句演示了可选 SELECT 子句的使用

FROM tbl;

这等同于

SELECT *
FROM tbl;

s	i
a	1
a	2
b	1
b	2

语法

关于此页面

行为准则商标使用指南

关于此页面

本文中