⌘+k ctrl+k
1.1.3 (stable)
Search Shortcut cmd + k | ctrl + k
FROM and JOIN Clauses

FROM 子句指定了查询其余部分应操作的数据的来源。从逻辑上讲，FROM 子句是查询开始执行的地方。FROM 子句可以包含单个表、使用 JOIN 子句连接在一起的多个表的组合，或者子查询节点中的另一个 SELECT 查询。DuckDB 还有一个可选的 FROM-first 语法，使您也可以在没有 SELECT 语句的情况下进行查询。

Examples

Select all columns from the table called table_name:

SELECT *
FROM table_name;

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

FROM table_name
SELECT *;

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

FROM table_name;

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

SELECT tn.*
FROM table_name tn;

从模式 schema_name 中的表 table_name 中选择所有列：

SELECT *
FROM schema_name.table_name;

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

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

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

SELECT *
FROM 'test.csv';

从子查询中选择所有列：

SELECT *
FROM (SELECT * FROM table_name);

选择表格的整行作为一个结构体：

SELECT t
FROM t;

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

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

将两个表连接在一起：

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

从表中选择10%的样本：

SELECT *
FROM table_name
TABLESAMPLE 10%;

从表中选择10行的样本：

SELECT *
FROM table_name
TABLESAMPLE 10 ROWS;

使用带有WHERE子句和聚合的FROM-first语法：

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

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;

自然连接

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

例如，以下是一个包含城市、机场代码和机场名称的示例。请注意，这两个表格都是故意不完整的，即它们在另一个表格中没有匹配的对。

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;

这将产生以下结果：

城市名称 IATA代码 机场名称
阿姆斯特丹 AMS 阿姆斯特丹史基浦机场
鹿特丹 RTM 鹿特丹海牙机场

请注意，只有在两个表中都存在相同iata属性的行才会被包含在结果中。

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

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

半连接和反连接

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

半连接示例

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

SELECT *
FROM city_airport
SEMI JOIN airport_names
    USING (iata);
城市名称 国际航空运输协会代码
阿姆斯特丹 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);
城市名称 国际航空运输协会代码
埃因霍温 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 关键字的使用变为可选。

位置连接

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

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

在处理时间或类似顺序数据时，一个常见的操作是在参考表（如价格）中查找最近（第一个）的事件。这被称为即时连接：

将价格附加到股票交易中：

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

返回符号、交易时间、价格（但不包括价格时间）：

如果你将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");

自连接

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

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

Binder Error: Duplicate alias "t" in query!

添加别名后，查询可以成功解析：

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

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

FROM-First Syntax with a SELECT Clause

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

FROM tbl
SELECT i, s;

这相当于：

SELECT i, s
FROM tbl;
i s
1 a
2 a
1 b
2 b

FROM-First 语法没有 SELECT 子句

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

FROM tbl;

这相当于：

SELECT *
FROM tbl;
s i
a 1
a 2
b 1
b 2

Syntax

关于此页面