表表达式

表表达式计算一个表,它包含一个FROM子句, 该子句可以根据需要选用WHERE, GROUP BY,HAVING 子句。大部分表表达式只是指向磁盘上的一个所谓的基本表, 但是我们可以用更复杂的表达式以各种方法修改或组合基本表。

表表达式里的WHERE, GROUP BY, HAVING 子句声明一系列对源自FROM子句的表的转换操作。 所有这些转换最后生成一个虚拟表,传递给选择列表计算输出行。

FROM子句

FROM 子句从一个逗号分隔的表引用列表中生成一个虚拟表。

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

表引用可以是一个表名字(可能有模式修饰)或者是一个生成的表, 比如子查询、表连接、或这些东西的复杂组合。如果在FROM子句中列出了多于一个表, 那么它们交叉连接(见下文)形成一个派生表,该表可以进行WHERE, GROUP BY, HAVING子句的转换处理,并最后生成表表达式的结果。

如果一个表引用是一个简单的父表的名字,那么将包括其所有后代子表的行, 除非你在该表名字前面加ONLY关键。然而,表引用只返回声明表中的列,其子表的列会被忽略。

连接表

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

连接类型

交叉连接

T1 CROSS JOIN T2

对每个来自T1和T2 的行进行组合(也就是,一个笛卡尔积),连接成的表将包含这样的行: 所有T1里面的列后面跟着所有T2 里面的列。如果两表分别有 N 和 M 行,连接成的表将有 N*M 行。

FROM T1 CROSS JOIN T2 等效于FROM T1,T2。 它还等效于FROM T1 INNER JOIN T2 ON TRUE(见下文)。

条件连接

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隐含地声明。 连接条件判断来自两个源表中的哪些行是”匹配”的,这些我们将在下面详细解释。

ON子句是最常见的连接条件的类型:它接收一个和WHERE 子句相同的布尔表达式。如果两个分别来自T1和T2 的行在ON表达式上运算的结果为真,那么它们就算是匹配的行。

USING是一个连接条件的缩写语法:它接收一个用逗号分隔的列名列表, 这些列必须是连接表共有的并且其值必须相同。最后,JOIN USING 会将每一对相等的输入列输出为一个列,其后跟着所有其它列。 因此,USING (a, b, c) 等效于ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON,那么在结果里a, b和c 列都会有两个,而用USING的时候就只会有一个(如果使用了SELECT * 的话,他们会优先发生)。

最后,NATURAL是USING的缩写形式: 它自动形成一个由两个表中同名的列组成的USING列表(同名列只出现一次)。 如果没有同名的列,NATURAL的行为会像CROSS

条件连接可能的类型是:

INNER JOIN

内连接。对于 T1 中的每一行 R1 ,如果能在 T2 中找到一个或多个满足连接条件的行, 那么这些满足条件的每一行都在连接表中生成一行。

LEFT OUTER JOIN

左外连接。首先执行一次内连接。然后为每一个 T1 中无法在 T2 中找到匹配的行生成一行, 该行中对应 T2 的列用 NULL 补齐。因此,生成的连接表里总是包含来自 T1 里的每一行至少一个副本。

RIGHT OUTER JOIN

右外连接。首先执行一次内连接。然后为每一个 T2 中无法在 T1 中找到匹配的行生成一行, 该行中对应 T1 的列用 NULL 补齐。因此,生成的连接表里总是包含来自 T2 里的每一行至少一个副本。

FULL OUTER JOIN

全连接。首先执行一次内连接。然后为每一个 T1 与 T2 中找不到匹配的行生成一行, 该行中无法匹配的列用 NULL 补齐。因此,生成的连接表里无条件地包含 T1 和 T2 里的每一行至少一个副本。

如果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)

表和列别名

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

要创建一个表别名,可以这样:

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;

在OushuDB中,如果add_missing_from环境变量是off(缺省时),会抛出一个错误。如果是on,一个隐含的表引用将会被加到FROM子句里,所以这个查询可以写成这样:

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

这样通常会导致交叉连接,但通常不是想要的结果。

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

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

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

圆括弧用于解决歧义。下面的第一个语句把别名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的外面是看不到的。

子查询

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

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列表中的列取别名, 并且被认为是一个好习惯。更多信息参见 VALUES列表

表函数

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

如果表函数返回基本数据类型,那么单一结果列的名字匹配函数名。 如果表函数返回复合数据类型,那么多个结果列的名字和该类型的每个属性的名字相同。

可以在FROM子句中为表函数取一个别名,也可以不取别名。 如果一个函数在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。如果在查询里使用这样的函数, 那么我们必须在查询中声明预期的行结构,这样系统才知道如何分析和规划该查询。

WHERE子句

WHERE 子句的语法是:

WHERE search_condition

这里的search_condition是任意返回类型为 boolean 的值表达式(参阅 值表达式 )。

在完成对FROM子句的处理之后,生成的每一行都会按照搜索条件进行检查。 如果结果是真,那么该行保留在输出表中,否则(也就是结果是假或NULL)就把它抛弃。 搜索条件通常至少要引用一列在FROM子句里生成的列,这不是必须的, 但如果不这样的话,WHERE子句就没什么意义了。

Note

内连接的连接条件既可以写在WHERE子句里也可以写在JOIN子句里。 比如,下面的表表达式是等效的:

FROM a, b WHERE a.id = b.id AND b.val > 5

和:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

或者可能还有:

FROM a NATURAL JOIN b WHERE b.val > 5

你想用哪个只是风格问题。FROM子句里的JOIN 语法可能不那么容易移植到其它产品中。对于其他连接,它们没有其他选择:必须在FROM子句中, 外连接的ON或USING子句 不等于 WHERE条件, 因为它导致最终结果中行的增(那些不匹配的输入行)和删。

这里是一些WHERE子句的例子:

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

那些不符合WHERE子句的搜索条件的行将从fdt中删除。请注意我们把标量子查询当做一个值表达式来用。就像其它查询一样, 子查询里也可以使用复杂的表表达式。把c1修饰成fdt.c1 只有在c1是该子查询生成的列名字时才是必须的, 但修饰列名字可以增加语句的准确性(即使有时不是必须的)。 这个例子就演示了列名字范围如何从外层查询扩展到它的内层查询。

GROUP BY和HAVING子句

在通过了WHERE过滤器之后,生成的输入表可以继续用GROUP BY 子句进行分组,然后用HAVING子句选取一些分组行。

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [, grouping_column_reference]...

GROUP BY 子句 用于将一个表中所有列出的列值都相等的行分成一组。 列值列出的顺序没什么关系。 效果是将每个拥有相同值的行集合并为一组,代表该组中的所有行。 这样就可以删除输出里的重复,和/或计算应用于这些组的聚合。 比如:

=> SELECT * FROM test1;
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

=> SELECT x FROM test1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

在第二个查询里,我们不能写成SELECT * FROM test1 GROUP BY x, 因为字段y里没有哪个值可以和每个组关联起来。 被分组的列可以在选择列表中使用是因为它们每个组都有单一的数值。

通常,如果一个表被分了组,不在GROUP BY中列出的列不会出现在选择列表除非使用了聚合表达式。 一个带聚合表达式的例子是:

=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

这里的sum是一个聚合函数,它在组上计算总和。 有关可用的聚合函数的更多信息可以在 聚合函数 中找到。

Tip

没有有效的聚合表达式的分组可以计算一列中不同值的设置。 这个可以通过DISTINCT子句来实现(参考 DISTINCT )。

这里是另外一个例子:它计算每种产品的总销售额(而不是所有产品的总销售额)。

SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;

在这个例子里,列product_id,p.name 和p.price必须在GROUP BY子句里, 因为它们都在查询选择列表里被引用了。(取决于products表是如何设计的,name和price可能完全依赖于产品ID,所以额外的分组理论上是不必要的,但是这仍未实现。)s.units列不必在 GROUP BY列表里,因为它只是在一个聚合表达式(sum(…))里使用, 它代表一组产品的销售总额。对于每种产品,这个查询都返回一个该产品的总销售额。

在严格的SQL里,GROUP BY只能对源表的列进行分组,但 OushuDB 把这个扩展为也允许GROUP BY对选择列表中的列进行分组。也允许对值表达式进行分组,而不仅仅是简单的列。

如果一个表已经用GROUP BY分了组, 然后你又只对其中的某些组感兴趣,那么就可以用HAVING子句筛选分组。 必须像WHERE子句,从结果中消除组,语法是:

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

在HAVING子句中的表达式可以引用分组的表达式和未分组的表达式 (后者必须涉及一个聚合函数)。

例子:

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

然后是一个更现实的例子:

SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) > 5000;

在上面的例子里,WHERE子句根据未分组的列选择数据行 (表达式只是对那些最近四周发生的销售为真)。而HAVING 子句在分组之后选择那些销售总额超过5000的组。 请注意聚合表达式不需要在查询中的所有地方都一样。