JOIN
用显式JOIN子句控制优化器
我们可以在一定程度上用显式JOIN语法控制查询优化器。要明白为什么需要它，我们首先需要一些背景知识。
在一个简单的连接查询中，例如：
1SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;优化器可以自由地按照任何顺序连接给定的表。例如，它可以生成一个使用WHERE条件a.id = b.id连接 A 到 B的查询计划，然后用另外一个WHERE条件把 C 连接到这个连接表。或者它可以先连接 B 和 C然后再连接 A 得到同样的结果。 或者也可以连接 A 到 C 然后把结果与 B 连接,不过这么做效率不好，因为必须生成完整的 A 和 C的迪卡尔积，而在WHERE子句中没有可用条件来优化该连接（PalopgSQL执行器中的所有连接都发生在两个输入表之间，
所以它必须以这些形式之一建立结果）。 重要的一点是这些不同的连接可能性给出在语义等效的结果，但在执行开销上却可能有巨大的差别。因此，优化器会对它们进行探索并尝试找出最高效的查询计划。
当一个查询只涉及两个或三个表时，那么不需要考虑很多连接顺序。但是可能的连接顺序数随着表数目的增加成指数增长。当超过十个左右的表以后，实际上根本不可能对所有可能性做一次穷举搜索，甚至对六七个表都需要相当长的时间进行规划。
当有太多的输入表时，PalopgSQL优化器将从穷举搜索切换为一种遗传概率搜索，它只需要考虑有限数量的可能性（切换的阈值用geqo_threshold运行时参数设置）。遗传搜索用时更少，但是并不一定会找到最好的计划。
当查询涉及外连接时，优化器比处理普通（内）连接时拥有更小的自由度。例如，考虑：
1SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);尽管这个查询的约束表面上和前一个非常相似，但它们的语义却不同， 因为如果 A 里有任何一行不能匹配 B 和C的连接表中的行，它也必须被输出。因此这里优化器对连接顺序没有什么选择：它必须先连接B 到 C，然后把 A 连接到该结果上。
相应地，这个查询比前面一个花在规划上的时间更少。在其它情况下，优化器就有可能确定多种连接顺序都是安全的。例如，给定：
1SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);将 A 首先连接到 B 或 C 都是有效的。当前，只有FULL JOIN完全约束连接顺序。大多数涉及LEFT JOIN或RIGHT JOIN的实际情况都在某种程度上可以被重新排列。
显式连接语法（INNER JOIN、CROSS JOIN或无修饰的JOIN）在语义上和FROM中列出输入关系是一样的，因此它不约束连接顺序。
即使大多数类型的JOIN并不完全约束连接顺序，但仍然可以指示PalopgSQL查询优化器将所有JOIN子句当作有连接顺序约束来对待。例如，这里的三个查询在逻辑上是等效的：
1SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
2SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
3SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);但如果我们告诉优化器遵循JOIN的顺序，那么第二个和第三个还是要比第一个花在规划上的时间少。这个效果对于只有三个表的连接而言是微不足道的，但对于数目众多的表，可能就是救命稻草了。
要强制优化器遵循显式JOIN的连接顺序，我们可以把运行时参数join_collapse_limit设置为1（其它可能值在下文讨论)。
你不必为了缩短搜索时间来完全约束连接顺序，因为可以在一个普通FROM列表里使用JOIN操作符。例如，考虑：
1SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;如果设置join_collapse_limit = 1，那么这就强迫优化器先把 A 连接到 B，然后再连接到其它的表上，但并不约束它的选择。在这个例子中，可能的连接顺序的数目减少了5 倍。
按照这种方法约束优化器的搜索是一个有用的技巧，不管是对减少规划时间还是对引导优化器生成好的查询计划。
如果优化器按照默认选择了一个糟糕的连接顺序，你可以通过JOIN语法强迫它选择一个更好的顺序。假设你知道一个更好的顺序。我们推荐进行实验。
一个非常相近的影响规划时间的问题是把子查询压缩到它们的父查询中。例如，考虑：
1SELECT *
2FROM x, y,
3 (SELECT * FROM a, b, c WHERE something) AS ss
4WHERE somethingelse;这种情况可能在使用包含连接的视图时出现；该视图的SELECT规则将被插入到引用视图的地方，得到与上文非常相似的查询。
通常，优化器会尝试把子查询压缩到父查询里，得到：
1SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;这样通常会生成一个比独立的子查询更好些的计划（例如，outer 的WHERE条件可能先把 X 连接到 A 上，这样就消除了 A 中的许多行，因此避免了形成子查询的全部逻辑输出）。但是同时，我们增加了规划的时间；在这里，我们用五路连接问题替代了两个独立的三路连接问题。这样的差别是巨大的，因为可能的计划数的是按照指数增长的。如果有超过from_collapse_limit个FROM项将会导致父查询，优化器将尝试通过停止提升子查询来避免卡在巨大的连接搜索问题中。你可以通过调高或调低这个运行时参数在规划时间和计划的质量之间取得平衡。
from_collapse_limit和join_collapse_limit的命名相似，因为它们做的几乎是同一件事：一个控制优化器何时将把子查询“平面化”，另外一个控制何时把显式连接平面化。通常，你要么把join_collapse_limit设置成和from_collapse_limit一样（这样显式连接和子查询的行为类似），要么把join_collapse_limit设置为 1（如果你想用显式连接控制连接顺序）。但是你可以把它们设置成不同的值，这样你就可以细粒度地调节规划时间和运行时间之间的平衡。
表表达式
表表达式 计算一个表。该表表达式包含一个FROM子句，该子句后面可以根据需要选用WHERE、GROUP BY和HAVING子句。最简单的表表达式只是引用磁盘上的一个表，一个所谓的基本表，但是我们可以用更复杂的表表达式以多种方法修改或组合基本表。
表表达式里可选的WHERE、GROUP BY和HAVING子句指定一系列对源自FROM子句的表的转换操作。所有这些转换最后生成一个虚拟表，它提供行传递给选择列表计算查询的输出行。
FROM子句
FROM子句 从一个用逗号分隔的表引用列表中的一个或更多个其它表中生成一个表。
1FROM table_reference [, table_reference [, ...]]表引用可以是一个表名字（可能有模式限定）或者是一个生成的表，例如子查询、一个JOIN结构或者这些东西的复杂组合。如果在FROM子句中引用了多于一个表，那么它们被交叉连接（即构造它们的行的笛卡尔积，见下文）。FROM列表的结果是一个中间的虚拟表，该表可以进行由WHERE、GROUP BY和HAVING子句指定的转换，并最后生成全局的表表达式结果。
如果一个表引用是一个简单的表名字并且它是表继承层次中的父表，那么该表引用将产生该表和它的后代表中的行，除非你在该表名字前面放上ONLY关键字。但是，这种引用只会产生出现在该命名表中的列在子表中增加的列都会被忽略。
除了在表名前写ONLY，你可以在表名后面写上*来显式地指定要包括所有的后代表。没有实际的理由再继续使用这种语法，因为搜索后代表现在总是默认行为。不过，为了保持与旧版本的兼容性，仍然支持这种语法。
连接表
一个连接表是根据特定的连接类型的规则从两个其它表（真实表或生成表）中派生的表。目前支持内连接、外连接和交叉连接。一个连接表的一般语法是：
1T1 join_type T2 [ join_condition ]所有类型的连接都可以被链在一起或者嵌套：T1和T2都可以是连接表。在JOIN子句周围可以使用圆括号来控制连接顺序。如果不使用圆括号，JOIN子句会从左至右嵌套。
连接类型
cross连接
: 对来自于 T1 和 T2 的行的每一种可能的组合（即笛卡尔积），连接表将包含这样一行：它由所有 T1 里面的列后面跟着所有 T2 里面的列构成。如果两个表分别有
N 和 M 行，连接表将有 N * M 行。
1``` sql
2T1 CROSS JOIN T2
3```
4
5`FROM T1` CROSS JOIN *`T2`*等效于`FROM T1` INNER JOIN *`T2`* ON TRUE（见下文）。它也等效于`FROM T1`,*`T2`*。
6
7**注意**
8
9当多于两个表出现时，后一种等效并不严格成立，因为`JOIN`比逗号绑得更紧。例如`FROM T1` CROSS JOIN *`T2`* INNER JOIN *`T3`* ON *`condition`*和`FROM T1`,*`T2`* INNER JOIN *`T3`* ON *`condition`*并不完全相同，因为第一种情况中的*`condition`*可以引用*`T1`*，但在第二种情况中却不行。条件连接
: INNER和OUTER对所有连接形式都是可选的。INNER是缺省；LEFT、RIGHT和FULL指示一个外连接。
1``` sql
2T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
3T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
4T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2
5```
6
7*连接条件*在`ON`或`USING`子句中指定， 或者用关键字`NATURAL`隐含地指定。连接条件决定来自两个源表中的哪些行是“匹配”的，这些我们将在后文详细解释。
8
9可能的条件连接类型是：
10
11`INNER JOIN`
12: 对于 T1 的每一行 R1，生成的连接表都有一行对应 T2 中的每一个满足和 R1 的连接条件的行。
13
14`LEFT OUTER JOIN`
15: 首先，执行一次内连接。然后，为 T1 中每一个无法在连接条件上匹配 T2 里任何一行的行返回一个连接行，该连接行中 T2的列用空值补齐。因此，生成的连接表里为来自 T1 的每一行都至少包含一行。
16
17`RIGHT OUTER JOIN`
18: 首先，执行一次内连接。然后，为 T2 中每一个无法在连接条件上匹配 T1 里任何一行的行返回一个连接行，该连接行中 T1的列用空值补齐。因此，生成的连接表里为来自 T2 的每一行都至少包含一行。
19
20`FULL OUTER JOIN`
21: 首先，执行一次内连接。然后，为 T1 中每一个无法在连接条件上匹配 T2 里任何一行的行返回一个连接行，该连接行中 T2的列用空值补齐。同样，为 T2 中每一个无法在连接条件上匹配 T1 里任何一行的行返回一个连接行，该连接行中T1 的列用空值补齐。
22
23
24`ON`子句是最常见的连接条件的形式：它接收一个和`WHERE`子句里用的一样的布尔值表达式。
25
26如果两个分别来自 *`T1`* 和 *`T2`* 的行在`ON`表达式上运算的结果为真，那么它们就算是匹配的行。
27
28`USING`是个缩写符号，它允许你利用特殊的情况：连接的两端都具有相同的连接列名。它接受共享列名的一个逗号分隔列表，并且为其中每一个共享列构造一个包含等值比较的连接条件。例如用`USING (a, b)`连接*`T1`*和*`T2`*会产生连接条件`ON T1`.a = *`T2`*.a AND *`T1`*.b = *`T2`*.b。
29
30更进一步，`JOIN USING`的输出会废除冗余列：不需要把匹配上的列都打印出来，因为它们必须具有相等的值。不过`JOIN ON`会先产生来自 *`T1`* 的所有列，后面跟上所有来自 *`T2`* 的列；而`JOIN USING`会先为列出的每一个列对产生一个输出列，然后先跟上来自 *`T1`* 的剩余列，最后跟上来自 *`T2`* 的剩余列。
31
32
33
34最后，`NATURAL`是`USING`的缩写形式：它形成一个`USING`列表，该列表由那些在两个表里都出现了的列名组成。和`USING`一样，这些列只在输出表里出现一次。如果不存在公共列，`NATURAL JOIN`的行为将和`JOIN ... ON TRUE`一样产生交叉集连接。
35
36
37
38**注意**
39
40`USING`对于连接关系中的列改变是相当安全的，因为只有被列出的列会被组合成连接条件。`NATURAL`的风险更大，因为如果其中一个关系的模式改变会导致出现一个新的匹配列名，就会导致连接将新列也组合成连接条件。为了解释这些问题，假设我们有一个表t1：
1 num | name
2-----+------
3 1 | a
4 2 | b
5 3 | c和t2表：
1 num | value
2-----+-------
3 1 | xxx
4 3 | yyy
5 5 | zzz然后我们用不同的连接方式可以获得各种结果：
1=> SELECT * FROM t1 CROSS JOIN t2;
2 num | name | num | value
3-----+------+-----+-------
4 1 | a | 1 | xxx
5 1 | a | 3 | yyy
6 1 | a | 5 | zzz
7 2 | b | 1 | xxx
8 2 | b | 3 | yyy
9 2 | b | 5 | zzz
10 3 | c | 1 | xxx
11 3 | c | 3 | yyy
12 3 | c | 5 | zzz
13(9 rows)
14
15=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
16 num | name | num | value
17-----+------+-----+-------
18 1 | a | 1 | xxx
19 3 | c | 3 | yyy
20(2 rows)
21
22=> SELECT * FROM t1 INNER JOIN t2 USING (num);
23 num | name | value
24-----+------+-------
25 1 | a | xxx
26 3 | c | yyy
27(2 rows)
28
29=> SELECT * FROM t1 NATURAL INNER JOIN t2;
30 num | name | value
31-----+------+-------
32 1 | a | xxx
33 3 | c | yyy
34(2 rows)
35
36=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
37 num | name | num | value
38-----+------+-----+-------
39 1 | a | 1 | xxx
40 2 | b | |
41 3 | c | 3 | yyy
42(3 rows)
43
44=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
45 num | name | value
46-----+------+-------
47 1 | a | xxx
48 2 | b |
49 3 | c | yyy
50(3 rows)
51
52=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
53 num | name | num | value
54-----+------+-----+-------
55 1 | a | 1 | xxx
56 3 | c | 3 | yyy
57 | | 5 | zzz
58(3 rows)
59
60=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
61 num | name | num | value
62-----+------+-----+-------
63 1 | a | 1 | xxx
64 2 | b | |
65 3 | c | 3 | yyy
66 | | 5 | zzz
67(4 rows)用ON指定的连接条件也可以包含与连接不直接相关的条件。这种功能可能对某些查询很有用，但是需要我们仔细想清楚。例如：
1=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
2 num | name | num | value
3-----+------+-----+-------
4 1 | a | 1 | xxx
5 2 | b | |
6 3 | c | |
7(3 rows)注意
把限制放在WHERE子句中会产生不同的结果：
1=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
2 num | name | num | value
3-----+------+-----+-------
4 1 | a | 1 | xxx
5(1 row)这是因为放在ON子句中的一个约束在连接之前被处理，而放在WHERE子句中的一个约束是在连接之后被处理。这对内连接没有关系，但是对于外连接会带来麻烦。
表和列别名
你可以给一个表或复杂的表引用指定一个临时的名字，用于剩下的查询中引用那些派生的表。这被叫做表别名。
要创建一个表别名，我们可以写：
1FROM table_reference AS alias或者
1FROM table_reference aliasAS关键字是可选的。别名可以是任意标识符。
表别名的典型应用是给长表名赋予比较短的标识符， 好让连接子句更易读。例如：
1SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;到这里，别名成为当前查询的表引用的新名称 — 我们不再能够用该表最初的名字引用它了。因此，下面的用法是不合法的：
1SELECT * FROM my_table AS m WHERE my_table.a > 5; -- 错误表别名主要用于简化符号，但是当把一个表连接到它自身时必须使用别名，例如：
1SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;此外，如果一个表引用是一个子查询，则必须要使用一个别名（见子查询章节）。
圆括弧用于解决歧义。在下面的例子中，第一个语句将把别名b赋给my_table的第二个实例，但是第二个语句把别名赋给连接的结果：
1SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
2SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...另外一种给表指定别名的形式是给表的列赋予临时名字，就像给表本身指定别名一样：
1FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )如果指定的列别名比表里实际的列少，那么剩下的列就没有被重命名。这种语法对于自连接或子查询特别有用。
如果用这些形式中的任何一种给一个JOIN子句的输出附加了一个别名， 那么该别名就在JOIN的作用下隐去了其原始的名字。例如：
1SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...是合法 SQL，但是：
1SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c是不合法的：表别名a在别名c外面是看不到的。
子查询
子查询指定了一个派生表，它必须被包围在圆括弧里并且必须被赋予一个表别名（参阅表和列别名章节）。例如：
1FROM (SELECT * FROM table1) AS alias_name这个例子等效于FROM table1 AS alias_name。更有趣的情况是在子查询里面有分组或聚集的时候，子查询不能被简化为一个简单的连接。
一个子查询也可以是一个VALUES列表：
1FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
2 AS names(first, last)再次的，这里要求一个表别名。为VALUES列表中的列分配别名是可选的，但是选择这样做是一个好习惯。更多信息可参见VALUES列表。
表函数
表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型（标量类型）组成，也可以是由复合数据类型（表行）组成。它们的用法类似一个表、视图或者在查询的FROM子句里的子查询。表函数返回的列可以像一个表列、视图或者子查询那样被包含在SELECT、JOIN或WHERE子句里。
也可以使用ROWS FROM语法将平行列返回的结果组合成表函数； 这种情况下结果行的数量是最大一个函数结果的数量，较小的结果会用空值来填充。
1function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
2ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]如果指定了WITH ORDINALITY子句，一个额外的 bigint类型的列将会被增加到函数的结果列中。这个列对函数结果集的行进行编号，编号从 1 开始（这是对 SQL 标准语法 UNNEST ... WITH ORDINALITY的一般化）。默认情 况下，序数列被称为ordinality，但也可以通过使用一个 AS子句给它分配一个不同的列名。
调用特殊的表函数UNNEST可以使用任意数量的数组参数， 它会返回对应的列数，就好像在每一个参数上单独调用UNNEST（数组函数和操作符）并且使用ROWS FROM结构把它们组合起来。
1UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]如果没有指定table_alias，该函数名将被用作 表名。在ROWS FROM()结构的情况中，会使用第一个函数名。
如果没有提供列的别名，那么对于一个返回基数据类型的函数，列名也与该函数 名相同。对于一个返回组合类型的函数，结果列会从该类型的属性得到名称。
例子：
1CREATE TABLE foo (fooid int, foosubid int, fooname text);
2
3CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
4 SELECT * FROM foo WHERE fooid = $1;
5$$ LANGUAGE SQL;
6
7SELECT * FROM getfoo(1) AS t1;
8
9SELECT * FROM foo
10 WHERE foosubid IN (
11 SELECT foosubid
12 FROM getfoo(foo.fooid) z
13 WHERE z.fooid = foo.fooid
14 );
15
16CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
17
18SELECT * FROM vw_getfoo;有时侯，定义一个能够根据它们被调用方式返回不同列集合的表函数是很有用的。为了支持这些，表函数可以被声明为返回伪类型record。
如果在查询里使用这样的函数，那么我们必须在查询中指定所预期的行结构，这样系统才知道如何分析和规划该查询。这种语法是这样的：
1function_call [AS] alias (column_definition [, ... ])
2function_call AS [alias] (column_definition [, ... ])
3ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )在没有使用ROWS FROM()语法时， column_definition 列表会取代无法附着在FROM项上的列别名列表，列定义中的名称就起到列别名的作用。 在使用ROWS FROM()语法时， 可以为每一个成员函数单独附着一个 column_definition 列表；或者在只有一个成员函数并且没有WITH ORDINALITY子句的情况下，可以在 ROWS FROM()后面写一个
column_definition 列表来取代一个列别名列表。
考虑下面的例子：
1SELECT *
2 FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
3 AS t1(proname name, prosrc text)
4 WHERE proname LIKE 'bytea%';dblink函数（dblink模块的一部分）执行一个远程的查询。它被声明为返回record，因为它可能会被用于任何类型的查询。
实际的列集必须在调用它的查询中指定，这样解析器才知道类似*这样的东西应该扩展成什么样子。
LATERAL子查询
可以在出现于FROM中的子查询前放置关键字LATERAL。这允许它们引用前面的FROM项提供的列（如果没有LATERAL，每一个子查询将被独立计算，并且因此不能被其他FROM项交叉引用）。
出现在FROM中的表函数的前面也可以被放上关键字LATERAL，但对于函数该关键字是可选的，在任何情况下函数的参数都可以包含对前面的FROM项提供的列的引用。
一个LATERAL项可以出现在FROM列表顶层，或者出现在一个JOIN树中。在后一种情况下，如果它出现在JOIN的右部，那么它也可以引用在JOIN左部的任何项。
如果一个FROM项包含LATERAL交叉引用，计算过程如下：对于提供交叉引用列的FROM项的每一行，或者多个提供这些列的多个FROM项的行集合，LATERAL项将被使用该行或者行集中的列值进行计算。得到的结果行将和它们被计算出来的行进行正常的连接。对于来自这些列的源表的每一行或行集，该过程将重复。
LATERAL的一个简单例子：
1SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;这不是非常有用，因为它和一种更简单的形式得到的结果完全一样：
1SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;在必须要使用交叉引用列来计算那些即将要被连接的行时，LATERAL是最有用的。一种常用的应用是为一个返回集合的函数提供一个参数值。例如，假设vertices(polygon)返回一个多边形的顶点集合，我们可以这样标识存储在一个表中的多边形中靠近的顶点：
1SELECT p1.id, p2.id, v1, v2
2FROM polygons p1, polygons p2,
3 LATERAL vertices(p1.poly) v1,
4 LATERAL vertices(p2.poly) v2
5WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;这个查询也可以被写成：
1SELECT p1.id, p2.id, v1, v2
2FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
3 polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
4WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;或者写成其他几种等价的公式（正如以上提到的，LATERAL关键字在这个例子中并不是必不可少的，但是我们在这里使用它是为了使表述更清晰）。
有时候也会很特别地把LEFT JOIN放在一个LATERAL子查询的前面，这样即使LATERAL子查询对源行不产生行，源行也会出现在结果中。例如，如果get_product_names()返回一个制造商制造的产品的名字，但是某些制造商在我们的表中目前没有制造产品，我们可以找出哪些制造商是这样：
1SELECT m.name
2FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
3WHERE pname IS NULL;WHERE子句
WHERE 子句的语法是
1WHERE search_condition这里的search_condition是任意返回一个boolean类型值的值表达式（参阅值表达式章节）。
在完成对FROM子句的处理之后，生成的虚拟表的每一行都会对根据搜索条件进行检查。
如果该条件的结果是真，那么该行被保留在输出表中；否则（也就是说，如果结果是假或空）就把它抛弃。搜索条件通常至少要引用一些在FROM子句里生成的列；虽然这不是必须的，但如果不引用这些列，那么WHERE子句就没什么用了。
注意
内连接的连接条件既可以写在WHERE子句也可以写在JOIN子句里。例如，这些表表达式是等效的：
1FROM a, b WHERE a.id = b.id AND b.val > 5和：
1FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5或者可能还有：
1FROM a NATURAL JOIN b WHERE b.val > 5你想用哪个只是一个风格问题。FROM子句里的JOIN语法可能不那么容易移植到其它SQL数据库管理系统中。
对于外部连接而言没有选择：它们必须在FROM子句中完成。
外部连接的ON或USING子句不等于WHERE条件，因为它导致最终结果中行的增加（对那些不匹配的输入行）和减少。
这里是一些WHERE子句的例子：
1SELECT ... FROM fdt WHERE c1 > 5
2
3SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
4
5SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
6
7SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
8
9SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
10
11SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)在上面的例子里，fdt是从FROM子句中派生的表。
那些不符合WHERE子句的搜索条件的行会被从fdt中删除。请注意我们把标量子查询当做一个值表达式来用。
和任何其它查询一样，子查询里可以使用复杂的表表达式。同时还请注意fdt在子查询中也被引用。只有在c1也是作为子查询输入表的生成表的列时，才必须把c1限定成fdt.c1。但限定列名字可以增加语句的清晰度，即使有时候不是必须的。这个例子展示了一个外层查询的列名范围如何扩展到它的内层查询。
GROUP BY和HAVING子句
在通过了WHERE过滤器之后，生成的输入表可以使用GROUP BY子句进行分组，然后用HAVING子句删除一些分组行。
1SELECT select_list
2 FROM ...
3 [WHERE ...]
4 GROUP BY grouping_column_reference [, grouping_column_reference]...GROUP BY子句被用来把表中在所列出的列上具有相同值的行分组在一起。这些列的列出顺序并没有什么关系。其效果是把每组具有相同值的行组合为一个组行，它代表该组里的所有行。这样就可以删除输出里的重复和/或计算应用于这些组的聚集。例如：
1=> SELECT * FROM test1;
2 x | y
3---+---
4 a | 3
5 c | 2
6 b | 5
7 a | 1
8(4 rows)
9
10=> SELECT x FROM test1 GROUP BY x;
11 x
12
13 a
14 b
15 c
16(3 rows)在第二个查询里，我们不能写成SELECT * FROM test1 GROUP BY x，因为列y里没有哪个值可以和每个组相关联起来。被分组的列可以在选择列表中引用是因为它们在每个组都有单一的值。
通常，如果一个表被分了组，那么没有在GROUP BY中列出的列都不能被引用，除非在聚集表达式中被引用。 一个用聚集表达式的例子是：
1=> SELECT x, sum(y) FROM test1 GROUP BY x;
2 x | sum
3---+-----
4 a | 4
5 b | 5
6 c | 2
7(3 rows)这里的sum是一个聚集函数，它在整个组上计算出一个单一值。有关可用的聚集函数的更多信息可以在聚集函数章节。
提示
没有聚集表达式的分组实际上计算了一个列中可区分值的集合。我们也可以用DISTINCT子句实现（参阅 DISTINCT章节）。
这里是另外一个例子：它计算每种产品的总销售额（而不是所有产品的总销售额）：
1SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
2 FROM products p LEFT JOIN sales s USING (product_id)
3 GROUP BY product_id, p.name, p.price;在这个例子里，列product_id、p.name和p.price必须在GROUP BY子句里，因为它们都在查询的选择列表里被引用到（但见下文）。列s.units不必在GROUP BY列表里，因为它只是在一个聚集表达式（sum(...)）里使用，它代表一组产品的销售额。对于每种产品，这个查询都返回一个该产品的所有销售额的总和行。
如果产品表被建立起来，例如product_id是主键，那么在上面的例子中用product_id来分组就够了，因为名称和价格都是函数依赖于产品ID，并且关于为每个产品ID分组返回哪个名称和价格值就不会有歧义。
在严格的 SQL 里，GROUP BY只能对源表的列进行分组，但PalopgSQL把这个扩展为也允许GROUP BY去根据选择列表中的列分组。也允许对值表达式进行分组，而不仅是简单的列名。
如果一个表已经用GROUP BY子句分了组，然后你又只对其中的某些组感兴趣，那么就可以用HAVING子句，它很象WHERE子句，用于从结果中删除一些组。其语法是：
1SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression在HAVING子句中的表达式可以引用分组的表达式和未分组的表达式（后者必须涉及一个聚集函数）。
例子：
1=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
2 x | sum
3---+-----
4 a | 4
5 b | 5
6(2 rows)
7
8=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
9 x | sum
10---+-----
11 a | 4
12 b | 5
13(2 rows)再次，一个更现实的例子：
1SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
2 FROM products p LEFT JOIN sales s USING (product_id)
3 WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
4 GROUP BY product_id, p.name, p.price, p.cost
5 HAVING sum(p.price * s.units) > 5000;在上面的例子里，WHERE子句用那些非分组的列选择数据行（表达式只是对那些最近四周发生的销售为真）。而HAVING子句限制输出为总销售收入超过5000的组。请注意聚集表达式不需要在查询中的所有地方都一样。
如果一个查询包含聚集函数调用，但是没有GROUP BY子句，分组仍然会发生：结果是一个单一行（或者根本就没有行，如果该单一行被HAVING所消除）。它包含一个HAVING子句时也是这样，即使没有任何聚集函数调用或者GROUP BY子句。
GROUPING SETS、CUBE和ROLLUP
使用分组集的概念可以实现比上述更加复杂的分组操作。由 FROM和WHERE子句选出的数据被按照每一个指定的分组集单独分组，按照简单GROUP BY子句对每一个分组计算聚集，然后返回结果。例如：
1=> SELECT * FROM items_sold;
2 brand | size | sales
3-------+------+-------
4 Foo | L | 10
5 Foo | M | 20
6 Bar | M | 15
7 Bar | L | 5
8(4 rows)
9
10=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
11 brand | size | sum
12-------+------+-----
13 Foo | | 30
14 Bar | | 20
15 | L | 15
16 | M | 35
17 | | 50
18(5 rows)GROUPING SETS的每一个子列表可以指定一个或者多个列或者表达式， 它们将按照直接出现在GROUP BY子句中同样的方式被解释。一个空的分组集表示所有的行都要被聚集到一个单一分组（即使没有输入行存在也会被输出）中，这就像前面所说的没有GROUP BY子句的聚集函数的情况一样。
对于分组列或表达式没有出现在其中的分组集的结果行，对分组列或表达式的引用会 被空值所替代。要区分一个特定的输出行来自于哪个分组，请见分组操作章节。
PalopgSQL 中提供了一种简化方法来指定两种常用类型的分组集。下面形式的子句
1ROLLUP ( e1, e2, e3, ... )表示给定的表达式列表及其所有前缀（包括空列表），因此它等效于
1GROUPING SETS (
2 ( e1, e2, e3, ... ),
3 ...
4 ( e1, e2 ),
5 ( e1 ),
6 ( )
7)这通常被用来分析历史数据，例如按部门、区和公司范围计算的总薪水。
下面形式的子句
1CUBE ( e1, e2, ... )表示给定的列表及其可能的子集（即幂集）。因此
1CUBE ( a, b, c )等效于
1GROUPING SETS (
2 ( a, b, c ),
3 ( a, b ),
4 ( a, c ),
5 ( a ),
6 ( b, c ),
7 ( b ),
8 ( c ),
9 ( )
10)CUBE或ROLLUP子句中的元素可以是表达式或者 圆括号中的元素子列表。在后一种情况中，对于生成分组集的目的来说，子列表被当做单一单元来对待。例如：
1CUBE ( (a, b), (c, d) )等效于
1GROUPING SETS (
2 ( a, b, c, d ),
3 ( a, b ),
4 ( c, d ),
5 ( )
6)并且
1ROLLUP ( a, (b, c), d )等效于
1GROUPING SETS (
2 ( a, b, c, d ),
3 ( a, b, c ),
4 ( a ),
5 ( )
6)CUBE和ROLLUP可以被直接用在 GROUP BY子句中，也可以被嵌套在一个 GROUPING SETS子句中。如果一个GROUPING SETS子句被嵌套在另一个同类子句中， 效果和把内层子句的所有元素直接写在外层子句中一样。
如果在一个GROUP BY子句中指定了多个分组项，那么最终的 分组集列表是这些项的叉积。例如：
1GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))等效于
1GROUP BY GROUPING SETS (
2 (a, b, c, d), (a, b, c, e),
3 (a, b, d), (a, b, e),
4 (a, c, d), (a, c, e),
5 (a, d), (a, e)
6)注意
在表达式中，结构(a, b)通常被识别为一个 a 行构造器章节。在GROUP BY子句中，这不会在表达式的顶层应用，并且 (a, b)会按照上面所说的被解析为一个表达式的列表。如果出于某种原因你在分组表达式中需要一个行构造器，请使用ROW(a, b)。
窗口函数处理
如果查询包含任何窗口函数，这些函数将在任何分组、聚集和HAVING过滤被执行之后被计算。也就是说如果查询使用了任何聚集、GROUP BY或HAVING，则窗口函数看到的行是分组行而不是来自于FROM/WHERE的原始表行。
当多个窗口函数被使用，所有在窗口定义中有句法上等效的PARTITION BY和ORDER BY子句的窗口函数被保证在数据上的同一趟扫描中计算。因此它们将会看到相同的排序顺序，即使ORDER BY没有唯一地决定一个顺序。但是，对于具有不同PARTITION BY或ORDER BY定义的函数的计算没有这种保证（在这种情况中，在多个窗口函数计算之间通常要求一个排序步骤，并且并不保证保留行的顺序，即使它的ORDER BY把这些行视为等效的）。
目前，窗口函数总是要求排序好的数据，并且这样查询的输出总是被根据窗口函数的PARTITION BY/ORDER BY子句的一个或者另一个排序。但是，我们不推荐依赖于此。如果你希望确保结果以特定的方式排序，请显式使用顶层的ORDER BY子句。
评价此篇文章