Kingbase-查询-表表达式1-FROM 子句

1 概述

从数据库中检索数据的过程或命令叫做查询。在 SQL 里 SELECT 命令用于指定查询。 SELECT 命令的一般语

法是

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

下面几个章节将详细介绍如何选择列表、表表达式和排序声明。

一个简单类型的查询为：

SELECT * FROM table1;

假设有一个表叫做 table1 ，这条命令将 table1 中检索所有行和所有用户定义的列（检索的方法取决于

客户端应用。例如， ksql 程序将在屏幕上显示一个 ASCII 形式的表格，而客户端库将提供函数来从检索结果

中抽取单个值）。选择列表声明 * 意味着所有表表达式提供的列。一个选择列表也可以选择可用列的一个子

集或者在使用它们之前对列进行计算。例如，如果 table1 有叫做 a 、 b 和 c 的列（可能还有其他），那么

可以用下面的查询：

SELECT a, b + c FROM table1;

（假设 b 和 c 都是数字数据类型）。参阅 选择列表 获取更多细节。

FROM table1 是一种非常简单的表表达式：它只读取了一个表。通常，表表达式可以是基本表、连接和子

查询组成的复杂结构。但也可以省略整个表表达式而把 SELECT 命令当做一个计算器：

SELECT 3 * 4 ;

如果选择列表里的表达式返回变化的结果，那么这就更有用了。例如，你可以用这种方法调用函数：

SELECT random();

2 表表达式

表表达式计算一个表。该表表达式包含一个 FROM 子句，该子句后面可以根据需要选用 WHERE 、 GROUP BY 和 HAVING 子句。最简单的表表达式仅引用磁盘上的一个表，一个基本表，但可以用更复杂的表表达式以多种方法修改或组合基本表。

表表达式里可选的 WHERE 、 GROUP BY 和 HAVING 子句指定一系列对源自 FROM 子句的表的转换操作。所

有这些转换最后生成一个虚拟表，它提供行传递给选择列表计算查询的输出行。

2.1 FROM 子句

FROM 子句从一个用逗号分隔的表引用列表中的一个或更多个其它表中生成一个表。

FROM table_reference [, table_reference [, ... ]]

表引用可以是一个表名字（可能有模式限定）或者是一个生成的表，例如子查询、一个 JOIN 结构或者这些

东西的复杂组合。如果在 FROM 子句中引用了多于一个表，那么它们被交叉连接（即构造它们的行的笛卡尔

积，见下文）。 FROM 列表的结果是一个中间的虚拟表，该表可以进行由 WHERE 、 GROUP BY 和 HAVING 子

句指定的转换，并最后生成全局的表表达式结果。

如果一个表引用是一个简单的表名字并且它是表继承层次中的父表，那么该表引用将产生该表和它的后代表

中的行，除非在该表名字前面放上 ONLY 关键字。但是，这种引用只会产生出现在该命名表中的列—在子表

中增加的列都会被忽略。

除了在表名前写 ONLY ，可以在表名后面写上 * 来显式地指定要包括所有的后代表。没有实际的理由再继

续使用这种语法，因为搜索后代表现在总是默认行为。不过，为了保持与旧版本的兼容性，仍然支持这种语

法。

2.1.1 连接表

一个连接表是根据特定的连接类型的规则从两个其它表（真实表或生成表）中派生的表。目前支持内连接、

外连接和交叉连接。一个连接表的一般语法是：

T1 join_type T2 [ join_condition ]

所有类型的连接都可以被链在一起或者嵌套： “T1“ 和 “T2“ 都可以是连接表。在 JOIN 子句周围可以使用圆括

号来控制连接顺序。如果不使用圆括号， JOIN 子句会从左至右嵌套。

连接类型

交叉连接

T1 CROSS JOIN T2

对来自于 “T1“ 和 “T2“ 的行的每一种可能的组合（即笛卡尔积），连接表将包含这样一行：它由所有

“T1“ 里面的列后面跟着所有 “T2“ 里面的列构成。如果两个表分别有 N 和 M 行，连接表将有 N * M 行。

FROM T1 CROSS JOIN “T2“ 等效于 FROM T1 INNER JOIN “T2“ ON TRUE （见下文）。它也等效于 FROM

T1 , “T2“ 。

Note: 当多于两个表出现时，后一种等效并不严格成立，因为 JOIN 比逗号绑得更紧。例如 FROM T1

CROSS JOIN “T2“ INNER JOIN “T3“ ON “condition“ 和 FROM T1 , “T2“ INNER JOIN “T3“ ON “condition“ 并

不完全相同，因为第一种情况中的 “condition“ 可以引用 “T1“ ，但在第二种情况中却不行。

条件连接

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON

boolean_expression

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join

column list )

T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

INNER 和 OUTER 对所有连接形式都是可选的。 INNER 是缺省； LEFT 、 RIGHT 和 FULL 指示一个外

连接。

连接条件在 ON 或 USING 子句中指定，或者用关键字 NATURAL 隐含地指定。连接条件决定来自两

个源表中的哪些行是“匹配”的，这些我们将在后文详细解释。

可能的条件连接类型是：

INNER JOIN 对于 T1 的每一行 R1 ，生成的连接表都有一行对应 T2 中的每一个满足和 R1 的连接条

件的行。

LEFT OUTER JOIN 首先，执行一次内连接。然后，为 T1 中每一个无法在连接条件上匹配 T2 里任何

一行的行返回一个连接行，该连接行中 T2 的列用空值补齐。因此，生成的连接表里为来自 T1 的

每一行都至少包含一行。

RIGHT OUTER JOIN 首先，执行一次内连接。然后，为 T2 中每一个无法在连接条件上匹配 T1 里任

何一行的行返回一个连接行，该连接行中 T1 的列用空值补齐。因此，生成的连接表里为来自 T2

的每一行都至少包含一行。

FULL OUTER JOIN 首先，执行一次内连接。然后，为 T1 中每一个无法在连接条件上匹配 T2 里任何

一行的行返回一个连接行，该连接行中 T2 的列用空值补齐。同样，为 T2 中每一个无法在连接条

件上匹配 T1 里任何一行的行返回一个连接行，该连接行中 T1 的列用空值补齐。

ON 子句是最常见的连接条件的形式：它接收一个和 WHERE 子句里用的一样的布尔值表达式。如果两

个分别来自 “T1“ 和 “T2“ 的行在 ON 表达式上运算的结果为真，那么它们就算是匹配的行。

USING 是个缩写符号，它允许你利用特殊的情况：连接的两端都具有相同的连接列名。它接受共享列

名的一个逗号分隔列表，并且为其中每一个共享列构造一个包含等值比较的连接条件。例如用 USING

(a, b) 连接 “T1“ 和 “T2“ 会产生连接条件 ON T1 .a = “T2“ .a AND “T1“ .b = “T2“ .b 。

更进一步， JOIN USING 的输出会废除冗余列：不需要把匹配上的列都打印出来，因为它们必须具有相

等的值。不过 JOIN ON 会先产生来自 “T1“ 的所有列，后面跟上所有来自 “T2“ 的列；而 JOIN USING

会先为列出的每一个列对产生一个输出列，然后先跟上来自 “T1“ 的剩余列，最后跟上来自 “T2“ 的剩余

列。

最后， NATURAL 是 USING 的缩写形式：它形成一个 USING 列表，该列表由那些在两个表里都出现

了的列名组成。和 USING 一样，这些列只在输出表里出现一次。如果不存在公共列， NATURAL JOIN

的行为将和 JOIN ... ON TRUE 一样产生交叉集连接。

Note: USING 对于连接关系中的列改变是相当安全的，因为只有被列出的列会被组合成连接条件。

NATURAL 的风险更大，因为如果其中一个关系的模式改变会导致出现一个新的匹配列名，就会导致连

接将新列也组合成连接条件。

为了解释这些问题，假设有一个表 t1 ：

num | name

-----+------

1 | a

2 | b

3 | c

和 t2 ：

num | value

-----+-------

1 | xxx

3 | yyy

5 | zzz

然后用不同的连接方式可以获得各种结果：

=> SELECT * FROM t1 CROSS JOIN t2;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

1 | a

|

3 | yyy

1 | a

|

5 | zzz

2 | b

|

1 | xxx

2 | b

|

3 | yyy

2 | b

|

5 | zzz

3 | c

|

1 | xxx

3 | c

|

3 | yyy

3 | c

|

5 | zzz

( 9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1 . num = t2 . num;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

3 | c

|

3 | yyy

( 2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);

num | name | value

-----+------+-------

1 | a

| xxx

3 | c

| yyy

( 2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;

num | name | value

-----+------+-------

1 | a

| xxx

3 | c

| yyy

( 2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1 . num = t2 . num;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

2 | b

|

|

3 | c

|

3 | yyy

( 3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);

num | name | value

-----+------+-------

1 | a

| xxx

2 | b

|

(continues on next page)

(continued from previous page)

3 | c

| yyy

( 3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1 . num = t2 . num;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

3 | c

|

3 | yyy

|

|

5 | zzz

( 3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1 . num = t2 . num;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

2 | b

|

|

3 | c

|

3 | yyy

|

|

5 | zzz

( 4 rows)

用 ON 指定的连接条件也可以包含与连接不直接相关的条件。例如：

=> SELECT * FROM t1 LEFT JOIN t2 ON t1 . num = t2 . num AND t2 . value = 'xxx' ;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

2 | b

|

|

3 | c

|

|

( 3 rows)

注意把限制放在 WHERE 子句中会产生不同的结果：

=> SELECT * FROM t1 LEFT JOIN t2 ON t1 . num = t2 . num WHERE t2 . value = 'xxx' ;

num | name | num | value

-----+------+-----+-------

1 | a

|

1 | xxx

( 1 row)

这是因为放在 ON 子句中的一个约束在连接之前被处理，而放在 WHERE 子句中的一个约束是在连接之后被

处理。这对内连接没有关系，但是对于外连接会带来麻烦。

2.1.2 表和列别名

可以给一个表或复杂的表引用指定一个临时的名字，用于剩下的查询中引用那些派生的表。这被叫做表别名。

创建一个表别名，可以：

FROM table_reference AS alias

或者

FROM table_reference alias

AS 关键字是可选的。 “ 别名 “ 可以是任意标识符。

表别名的典型应用是给长表名赋予比较短的标识符，让连接子句更易读。例如：

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON

s . id = a . num;

别名成为当前查询的表引用的新名称—不再能够用该表最初的名字引用它了。因此，下面的用法是不合法的：

SELECT * FROM my_table AS m WHERE my_table . a > 5 ;

-- 错误

表别名主要用于简化符号，但是当把一个表连接到它自身时必须使用别名，例如：

SELECT * FROM people AS mother JOIN people AS child ON mother . id =

child . mother_id;

此外，如果一个表引用是一个子查询，可使用一个别名，如果未指定则系统会自动生成一个别名（见 子查询

）。

圆括弧用于解决歧义。在下面的例子中，第一个语句将把别名 b 赋给 my_table 的第二个实例，但是第二

个语句把别名赋给连接的结果：

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...

SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

另外一种给表指定别名的形式是给表的列赋予临时名字，就像给表本身指定别名一样：

FROM table_reference [AS] alias ( column1 [, column2 [, ... ]] )

如果指定的列别名比表里实际的列少，那么剩下的列就没有被重命名。这种语法对于自连接或子查询特别有

用。

如果用这些形式中的任何一种给一个 JOIN 子句的输出附加了一个别名，那么该别名就在 JOIN 的作用下

隐去了其原始的名字。例如：

SELECT a .* FROM my_table AS a JOIN your_table AS b ON ...

是合法 SQL ，但是：

SELECT a .* FROM (my_table AS a JOIN your_table AS b ON ... ) AS c

是不合法的：表别名 a 在别名 c 外面是看不到的。

2.1.3 子查询

子查询指定了一个派生表，它必须被包围在圆括弧里，并且可以被赋予一个表别名，若未指定别名则系统会

自动生成一个别名（参阅 表和列别名 ）。例如：

FROM (SELECT * FROM table1) AS alias_name 或者 FROM (SELECT * FROM table1)

这个例子等效于 FROM table1 AS alias_name 。更有趣的情况是在子查询里面有分组或聚集的时候，

子查询不能被简化为一个简单的连接。

一个子查询也可以是一个 VALUES 列表：

FROM (VALUES ( 'anne' , 'smith' ), ( 'bob' , 'jones' ), ( 'joe' , 'blow' ))

AS names(first, last)

再次的，这里可以使用一个表别名，若未指定则系统自动生成一个别名。为 VALUES 列表中的列分配别名

是可选的，但是指定别名这样做是一个好习惯。

2.1.4 表函数

表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型（标量类型）组成，也可以是由复合

数据类型（表行）组成。它们的用法类似一个表、视图或者在查询的 FROM 子句里的子查询。表函数返回的

列可以像一个表列、视图或者子查询那样被包含在 SELECT 、 JOIN 或 WHERE 子句里。

也可以使用 ROWS FROM 语法将平行列返回的结果组合成表函数；这种情况下结果行的数量是最大一个函

数结果的数量，较小的结果会用空值来填充。

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(

column_alias [, ... ])]]

如果指定了 WITH ORDINALITY 子句，一个额外的 bigint 类型的列将会被增加到函数的结果列中。这个

列对函数结果集的行进行编号，编号从 1 开始（这是对 SQL 标准语法 UNNEST ... WITH ORDINALITY

的一般化）。默认情况下，序数列被称为 ordinality ，但也可以通过使用一个 AS 子句给它分配一个不同

的列名。

调用特殊的表函数 UNNEST 可以使用任意数量的数组参数，它会返回对应的列数，就好像在每一个参数上

单独调用 UNNEST （ 数组函数和操作符 ）并且使用 ROWS FROM 结构把它们组合起来。

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [( column_

, → alias [, ... ])]]

如果没有指定 “table_alias“ ，该函数名将被用作表名。在 ROWS FROM() 结构的情况中，会使用第一个函数

名。

如果没有提供列的别名，那么对于一个返回基数据类型的函数，列名也与该函数名相同。对于一个返回组合

类型的函数，结果列会从该类型的属性得到名称。

例子：

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$

SELECT * FROM foo WHERE fooid = $1;

$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo

WHERE foosubid IN (

SELECT foosubid

FROM getfoo(foo.fooid) z

WHERE z.fooid = foo.fooid

);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

有时侯，定义一个能够根据它们被调用方式返回不同列集合的表函数是很有用的。为了支持这些，表函数可

以被声明为返回伪类型 record 。如果在查询里使用这样的函数，那么必须在查询中指定所预期的行结构，

这样系统才知道如何分析和规划该查询。这种语法是这样的：

function_call [AS] alias (column_definition [, ... ])

function_call AS [alias] (column_definition [, ... ])

ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

在没有使用 ROWS FROM() 语法时， “column_definition“ 列表会取代无法附着在 FROM 项上的列别名列表，列

定义中的名称就起到列别名的作用。在使用 ROWS FROM() 语法时，可以为每一个成员函数单独附着一

个 “column_definition“ 列表；或者在只有一个成员函数并且没有 WITH ORDINALITY 子句的情况下，可以在

ROWS FROM() 后面写一个 “column_definition“ 列表来取代一个列别名列表。

考虑下面的例子：

SELECT *

FROM dblink( 'dbname=mydb' , 'SELECT proname, prosrc FROM sys_proc' )

AS t1(proname name, prosrc text)

WHERE proname LIKE 'bytea%' ;

dblink-function 函数执行一个远程的查询。它被声明为返回 record ，因为它可能会被用于任何类型的查询。

实际的列集必须在调用它的查询中指定，这样分析器才知道类似 * 这样的东西应该扩展成什么样子。

2.1.5 LATERAL 子查询

可以在出现于 FROM 中的子查询前放置关键词 LATERAL 。这允许它们引用前面的 FROM 项提供的列（如

果没有 LATERAL ，每一个子查询将被独立计算，并且因此不能被其他 FROM 项交叉引用）。

出现在 FROM 中的表函数的前面也可以被放上关键词 LATERAL ，但对于函数该关键词是可选的，在任何情

况下函数的参数都可以包含对前面的 FROM 项提供的列的引用。

一个 LATERAL 项可以出现在 FROM 列表顶层，或者出现在一个 JOIN 树中。在后一种情况下，如果它出

现在 JOIN 的右部，那么它也可以引用在 JOIN 左部的任何项。

如果一个 FROM 项包含 LATERAL 交叉引用，计算过程如下：对于提供交叉引用列的 FROM 项的每一行，或

者多个提供这些列的多个 FROM 项的行集合， LATERAL 项将被使用该行或者行集中的列值进行计算。得到

的结果行将和它们被计算出来的行进行正常的连接。对于来自这些列的源表的每一行或行集，该过程将重复。

LATERAL 的一个简单例子：

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar . id = foo . bar_id) ss;

这不是非常有用，因为它和一种更简单的形式得到的结果完全一样：

SELECT * FROM foo, bar WHERE bar . id = foo . bar_id;

在必须要使用交叉引用列来计算那些即将要被连接的行时， LATERAL 是最有用的。一种常用的应用是为一

个返回集合的函数提供一个参数值。例如，假设 vertices(polygon) 返回一个多边形的顶点集合，我们

可以这样标识存储在一个表中的多边形中靠近的顶点：

SELECT p1 . id, p2 . id, v1, v2

FROM polygons p1, polygons p2,

LATERAL vertices(p1 . poly) v1,

LATERAL vertices(p2 . poly) v2

WHERE (v1 <-> v2) < 10 AND p1 . id != p2 . id;

这个查询也可以被写成：

SELECT p1 . id, p2 . id, v1, v2

FROM polygons p1 CROSS JOIN LATERAL vertices(p1 . poly) v1,

polygons p2 CROSS JOIN LATERAL vertices(p2 . poly) v2

WHERE (v1 <-> v2) < 10 AND p1 . id != p2 . id;

或者写成其他几种等价的公式（正如以上提到的， LATERAL 关键词在这个例子中并不是必不可少的，但是

我们在这里使用它是为了使表述更清晰）。

有时候也会很特别地把 LEFT JOIN 放在一个 LATERAL 子查询的前面，这样即使 LATERAL 子查询对源

行不产生行，源行也会出现在结果中。例如，如果 get_product_names() 返回一个制造商制造的产品的

名字，但是某些制造商在我们的表中目前没有制造产品，我们可以找出哪些制造商是这样：

SELECT m . name

FROM manufacturers m LEFT JOIN LATERAL get_product_names(m . id) pname ON true

WHERE pname IS NULL;