FROM 与 JOIN 子句

FROM 子句用于指定查询后续部分要处理的数据来源。从逻辑上看，查询从 FROM 子句开始执行。FROM 子句可以包含单个表、多个通过 JOIN 子句连接的表组合，或子查询节点中的另一个 SELECT 查询。Goose 还支持可选的 FROM-first 语法，使你可以在没有 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_name 下的表 tbl 中选择所有列：

SELECT *
FROM schema_name.tbl;

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

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

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

SELECT *
FROM 'test.csv';

从子查询中选择所有列：

SELECT *
FROM (SELECT * FROM tbl);

将表的整行作为结构体选择：

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;

将 FROM-first 语法与 WHERE 子句和聚合结合使用：

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

表函数

Goose 中有些函数返回的是整张表，而不是单个值。这类函数称为表函数，可以像普通表引用一样与 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 window function 得到相同结果。 但在存在 joins 的情况下，WITH ORDINALITY 可以对连接的一侧进行编号，而不是对最终结果集编号，并且无需借助子查询。

连接

连接（join）是关系模型中的基础操作，用于将两张表或两个关系在水平方向上关联起来。 根据它们在连接子句中的书写位置，这两个关系分别称为连接的左侧与右侧。 结果中的每一行都包含来自两侧关系的列。

连接会使用某种规则为两侧关系匹配成对的行。 这个规则通常是谓词，但也可以通过其他隐含规则来指定。

外连接

如果指定 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;

将得到如下结果：

city_name	iata	airport_name
Amsterdam	AMS	Amsterdam Airport Schiphol
Rotterdam	RTM	Rotterdam The Hague Airport

注意，结果中只包含两张表都存在相同 iata 属性值的行。

也可以使用常规 JOIN 子句配合 USING 关键字来表达同一查询：

SELECT *
FROM city_airport
JOIN airport_names
USING (iata);

半连接与反连接

半连接返回左表中在右表里至少有一个匹配项的行。 反连接返回左表中在右表里没有匹配项的行。 使用半连接或反连接时，结果行数不会超过左表行数。 半连接的逻辑与 IN operator 一致。 反连接的逻辑与 NOT IN 一致，但反连接会忽略右表中的 NULL 值。

半连接示例

返回 city_airport 表中的城市-机场代码列表，要求该机场名称在 airport_names 表中存在：

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

city_name	iata
Amsterdam	AMS
Rotterdam	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
Eindhoven	EIN
Groningen	GRQ

该查询等价于：

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

LATERAL 连接

LATERAL 关键字允许 FROM 子句中的子查询引用前面的子查询。这个特性也称为 lateral join。

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

i	j
0	1
2	3
1	2

LATERAL 连接可以看作相关子查询的推广，因为它可以针对每个输入值返回多个值，而不仅是一个值。

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

Goose 会自动检测何时应使用 LATERAL 连接，因此 LATERAL 关键字是可选的。

位置连接

在处理等长的数据框或其他嵌入式表时， 行之间可能基于物理顺序存在天然对应关系。 在脚本语言中，这通常可以用循环轻松表达：

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

在标准 SQL 中表达这一点较困难，因为 关系表本身是无序的；但导入的数据（如 data frames 或磁盘文件，例如 CSVs 与 Parquet files）通常具有自然顺序。

按这种顺序进行关联称为位置连接：

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

在处理时间序列或类似有序数据时， 常见需求是从参考表（例如价格表）中找到最近（第一个）事件。 这称为 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 * 组合， 查询会返回匹配时左侧（probe）列的值， 而不是右侧（build）列的值。 若要在该示例中获取 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");

自连接

Goose 支持所有类型连接的自连接。 请注意，表必须使用别名；如果同一表名不加别名重复使用会报错：

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

Goose 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

语法