7.2. 表表达式

7.2.1.1. 连接表

一个连接表是根据特定的连接规则从两个其它表(真实表或生成表)中派生的表。 我们支持内连接、外连接、交叉连接。连接表的一般语法是

T1 join_type T2 [ join_condition ]

所有类型的连接都可以串连或嵌套在一起： T1和T2 之一或全部是可以连接的表。 你可以在JOIN子句周围使用圆括弧来控制连接顺序，如果没有圆括弧， 那么JOIN子句从左向右嵌套。

为了解释这些问题，假设我们有一个表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    |
   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子句中时是后于连接处理的。 这对内连接来说没什么问题，但是对外连接来说问题就大了。

7.2.1.2. 表和列别名

你可以给表或复杂的表引用起一个临时的表别名，以便被其余的查询引用。

要创建一个表别名，可以这样：

FROM table_reference AS alias

或：

FROM table_reference alias

AS关键字没啥特别的含义。alias可以是任意标识符。

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

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

表别名主要是为了方便标记，但对于自连接却是必须的。比如：

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

另外，要引用子查询的结果也必须使用别名(参见第 7.2.1.3 节)。

圆括弧用于解决歧义。下面的第一个语句把别名b赋予第二个my_table表； 而第二个语句则把别名b赋予了连接的结果。

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的外面是看不到的。

7.2.1.3. 子查询

子查询的结果(派生表)必须包围在圆括弧里并且必须赋予一个别名(参阅 第 7.2.1.2 节)。比如：

FROM (SELECT * FROM table1) AS alias_name

这个例子等效于FROM table1 AS alias_name。 更有趣的例子是在子查询里面有分组或聚合的时候， 这个时候子查询不能归纳成一个简单的连接。

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

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)

这种情况同样也必须要取一个别名。还可以为VALUES列表中的字段取别名， 并且被认为是一个好习惯。更多信息参见第 7.7 节。

7.2.1.4. 表函数

表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型(标量类型)组成， 也可以是由复合数据类型(表的行)组成。他们的用法类似一个表、视图、或 FROM 子句里的子查询。表函数返回的字段可以像一个表、视图、或者子查询的字段那样包含在 SELECT,JOIN,WHERE子句里。

使用ROWS FROM语法也可以组合表函数，结果返回并行的字段； 这种情况下的结果行数是较大函数的结果，较小的结果填充null值匹配。

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(第 9.18 节)， 并且使用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子句， 那么column_definition 列表可以替换跟随ROWS FROM()的字段别名列表。

考虑这个例子：

SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

dblink函数（dblink模块的一部分） 执行一个远程的查询。它声明为返回record，因为它可能会被用于任何类型的查询。 实际的字段集必须在调用它的查询中声明，这样分析器才知道类似* 这样的东西应该扩展成什么样子。

7.2.1.5. LATERAL 子查询

FROM子句中出现的子查询可以放在关键字LATERAL之前。 这样就允许它们引用通过前置FROM条目提供的字段。（如果没有LATERAL， 那么每个子查询都被认为是独立的并且不能交叉引用任何其他的FROM条目。）

FROM中出现的表函数也可以出现在关键字LATERAL之前， 但是对于函数来说，这个关键字是可选的；函数的参数在任何情况下都可以包含通过前置FROM 条目提供的字段。

LATERAL条目可以出现在FROM列表的顶级，或者在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当交叉引用字段对于加入的计算行是重要的时是主要有用的。 一个常见的应用是为一个set-returning函数提供一个参数值。例如，假设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;