?
This document uses PHP Chinese website manual Release
7.2. 表表達式
一個表表達式計算一個表，它包含一個FROM子句，該子句可以根據(jù)需要選用WHERE，GROUP BY和HAVING子句。大部分表表達式只是指向磁盤上的一個所謂的基本表，但是我們可以用更復雜的表表達式以各種方法修改或組合基本表。
表表達式里的WHERE，GROUP BY和HAVING子句聲明一系列對源自FROM子句的表的轉(zhuǎn)換操作。所有這些轉(zhuǎn)換最后生成一個虛擬表，傳遞給選擇列表計算輸出行。
7.2.1. FROM子句
FROM Clause從一個逗號分隔的表引用列表中生成一個虛擬表
FROM table_reference [， table_reference [， ...]]
表引用可以是一個表名字(可能有模式修飾)或者是一個生成的表，比如子查詢、表連接，或它們的復雜組合。如果在FROM子句中列出了多于一個表，那么它們被交叉連接(見下文)形成一個派生表，該表可以進行WHERE，GROUP BY和HAVING子句的轉(zhuǎn)換處理，并最后生成表表達式的結(jié)果。
如果一個表引用的是一個簡單的父表的名字，那么將包括其所有子表的行，除非你在該表名字前面加ONLY關(guān)鍵字(這樣任何子表都會被忽略)。
7.2.1.1. 連接表
一個連接表是根據(jù)特定的連接規(guī)則從兩個其它表(真實表或生成表)中派生的表。我們支持內(nèi)連接、外連接、交叉連接。
連接類型
- 交叉連接
-
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 T2INNER和OUTER對所有連接類型都是可選的。INNER為缺省。LEFT，RIGHT和FULL隱含外連接。
連接條件在ON或USING子句里聲明，或者用關(guān)鍵字NATURAL隱含地聲明。 連接條件判斷來自兩個源表中的那些行是"匹配"的，這些我們將在下面詳細解釋。
ON子句是最常見的連接條件的類型：它接收一個和WHERE子句相同的布爾表達式。 如果兩個分別來自T1和T2的行在ON表達式上運算的結(jié)果為真，那么它們就算是匹配的行。
USING是個一個連接條件的縮寫語法：它接收一個用逗號分隔的字段名列表， 這些字段必須是連接表共有的并且其值必須相同。最后，JOIN USING會將每 一對相等的輸入字段輸出為一個字段，其后跟著所有其它字段。因此，USING (a， b， c)等效 于ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)，只不過 是如果使用了ON，那么在結(jié)果里a，b和c字段都會有兩個， 而用USING的時候就只會有一個。(如果SELECT *被用的話他們會首先出現(xiàn))。
最后，NATURAL是USING的一種縮寫形式：它自動形成一個由兩個表中 同名的字段組成的USING列表(同名字段只出現(xiàn)一次)。
條件連接可能的類型是：
- INNER JOIN
內(nèi)連接。對于T1中的每一行R1，如果能在T2中找到一個或多個滿 足連接條件的行，那么這些滿足條件的每一行都在連接表中生成一行。
- LEFT OUTER JOIN
左外連接。首先執(zhí)行一次內(nèi)連接。然后為每一個T1中無法在T2中找 到匹配的行生成一行，該行中對應T2的列用NULL補齊。因此，生成的連接表里無條件地包含來自T1里的每一行至少一個副本。
- RIGHT OUTER JOIN
右外連接。首先執(zhí)行一次內(nèi)連接。然后為每一個T2中無法在T1中找到匹配的行生成一行，該行中對應T1的列用NULL補齊。因此， 生成的連接表里無條件地包含來自T2里的每一行至少一個副本。
- FULL OUTER JOIN
全連接。首先執(zhí)行一次內(nèi)連接。然后為每一個T1與T2中找不到匹配的 行生成一行，該行中無法匹配的列用NULL補齊。因此，生成的連接表里無條 件地包含T1和T2里的每一行至少一個副本。
如果T1和T2之一或全部是可以連接的表，那么所有類型的連接都可以串連或嵌套在一起。 你可以在JOIN子句周圍使用圓括弧來控制連接順序， 如果沒有圓括弧，那么JOIN子句從左向右嵌套。
為了解釋這些問題，假設(shè)我們有一個表t1
num | name -----+------ 1 | a 2 | b 3 | c
和 t2:
num | value -----+------- 1 | xxx 3 | yyy 5 | zzz
然后我們用不同的連接方式可以獲得各種結(jié)果：
=> 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聲明的連接條件也可以包含與連接不直接相關(guān)的條件。這種功能 可能對某些查詢很有用，但是需要我們仔細想清楚。比如：
=> 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條款的限制 產(chǎn)生不同的結(jié)果：
=> 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. 表和列別名
你可以給表或復雜的表引用起一個臨時的表別名，以便被其余的查詢引用，稱為tablealias.
要創(chuàng)建一個表別名，可以這樣：
FROM table_reference AS alias
或
FROM table_reference alias
AS關(guān)鍵字沒有特別的含義。alias可以是任意標識符。
表別名的典型應用是給長表名賦予比較短的標識，好讓連接子句更易讀一些。比如：
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
別名變成了與當前查詢有關(guān)的表的名字，之后就不允許再用最初的名字了。因此這是無效的
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;
另外，要引用子查詢的結(jié)果也必須使用別名(參見節(jié)Section 7.2.1.3)。
圓括弧用于解決歧義。下面的第一個語句把別名b賦予第二個my_table表； 而第二個語句則把別名b賦予了連接的結(jié)果。
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子句的輸出結(jié)果附加一個別名， 那么該別名就在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. 子查詢
子查詢的結(jié)果(派生表)必須包圍在圓括弧里并 且必須賦予一個別名(參閱Section 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列表中的字段取別名， 并且被認為是一個好習慣。更多信息參見Section 7.7。
7.2.1.4. 表函數(shù)
表函數(shù)是那些生成一個行集合的函數(shù)，這個集合可以是由基本數(shù)據(jù)類型(標量類型)組成， 也可以是由復合數(shù)據(jù)類型(表的行)組成。他們的用法類似一個表、視圖或FROM子 句里的子查詢。表函數(shù)返回的字段可以像一個表、視圖、或者子查詢字段那 樣包含在SELECT，JOIN或者WHERE子句里。
如果表函數(shù)返回基本數(shù)據(jù)類型，那么單一結(jié)果字段的名字與函數(shù)名相同。如果表函數(shù)返回復合 數(shù)據(jù)類型，那么多個結(jié)果字段的名字和該類型的每個屬性的名字相同。
可以在FROM子句中為表函數(shù)取一個別名，也可以不取別名。如果一個 函數(shù)在FROM子句中沒有別名，那么將使用函數(shù)名作為結(jié)果表的名字。
一些例子：
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;
有時侯，把一個函數(shù)定義成根據(jù)不同的調(diào)用方法可以返回不同的字段是很有用的。 為了支持這個，表函數(shù)可以聲明為返回偽類型record。如果在查詢里使用這樣 的函數(shù)，那么我們必須在查詢中聲明預期的行結(jié)構(gòu)，這樣系統(tǒng)才知道如何分析和 規(guī)劃該查詢。讓我們看看下面的例子：
SELECT *
FROM dblink('dbname=mydb'， 'SELECT proname， prosrc FROM pg_proc')
AS t1(proname name， prosrc text)
WHERE proname LIKE 'bytea%';
dblink函數(shù)執(zhí)行一個遠程的查詢(參閱contrib/dblink)。它聲明 為返回record，因為它可能會被用于任何類型的查詢。實際的字段集必 須在調(diào)用它的查詢中聲明，這樣分析器才知道類似*這樣的東西應該擴展成什么樣子。
7.2.2. WHERE子句
WHERE Clause子句的語法是
WHERE search_condition
這里的search_condition是一個返 回類型為boolean的值表達式(參閱Section 4.2)。
在完成對FROM子句的處理之后，生成的每一行都會按照search_condition進行檢查。 如果結(jié)果是真，那么該行保留在輸出表中，否則(結(jié)果是假或NULL)就把它拋棄。 搜索條件通常至少要引用一列在FROM子句里生成的列，這不是必須的， 但如果不這樣的話，WHERE子句就沒什么意義了。
Note: 內(nèi)連接的連接條件既可以寫在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語法可能不那么容易移植到其它產(chǎn)品中。即使它是在SQL標準 對于外連接而言，我們在任何情況下都沒有選擇：連接條件必須在FROM子句中完成。 外連接的ON或USING子句不等于WHERE條件，因為它判斷最終 結(jié)果中行的增(那些不匹配的輸入行)和刪。
這里是一些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)
在上面的例子里，fdt是從FROM子句中派生的表。那些不符合WHERE子句的搜 索條件的行將從fdt中刪除。請注意我們把標量子查詢當做一個值表達式來用。 就像其它查詢一樣，子查詢里也可以使用復雜的表表達式。同時還請注意fdt是 如何引用子查詢的。把c1修飾成fdt.c1只有在c1是該子查詢生成的列名字時才是必須的， 但修飾列名字可以增加語句的準確性(即使有時不是必須的)。這個例子就演示了字段名字 范圍如何從外層查詢擴展到它的內(nèi)層查詢。
7.2.3. GROUP BY和HAVING子句
在通過了WHERE過濾器之后，生成的輸出表可以繼續(xù)用GROUP BY子句進行 分組，然后用HAVING子句選取一些分組行
SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [， grouping_column_reference]...
GROUP BY Clause子句用于把那些所有列出的grouping_column_reference值都相 同的行聚集在一起，縮減為一行，這樣就可以刪除輸出里的重復和/或計算應用 于這些組的聚集。這些字段的列出順序無關(guān)緊要。比如：
=> 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里 沒有哪個值可以和每個組關(guān)聯(lián)起來。被分組的字段可以在選擇列表中引 用是因為它們每個組都有單一的數(shù)值。
如果一個表被分了組，不在GROUP BY列中除了在總表達式不能被引用，那么就只能引用聚集表達式中的字段和分組中的字段。一個帶聚集表達式的例子是：
=> SELECT x， sum(y) FROM test1 GROUP BY x; x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)
這里的sum是一個聚集函數(shù)，它在組上計算總和。 有關(guān)可用的聚集函數(shù)的更多信息可以在節(jié)Section 9.18中找到。
Tip: 沒有有效的聚合表達式分組可以計算一列中不同值的設(shè)置。 這個可以通過DISTINCT子句來實現(xiàn)(參考Section 7.3.3).
這里是另外一個例子：它計算每種產(chǎn)品的總銷售額(而不是所有產(chǎn)品的總銷售額)。
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子句里， 因為它們都在查詢選擇列表里被引用了。根據(jù)產(chǎn)品表具體設(shè)置的不同， 名字和價格可能和產(chǎn)品ID完全無關(guān)，因此理論上額外的分組可能是不必要的， 但是這些尚未實現(xiàn)。s.units字段不必在GROUP BY列表里，因為它只是在一個聚集表達式(sum(...))里使用，它代表一組產(chǎn)品的銷售總額。 對于每種產(chǎn)品，這個查詢都返回一個該產(chǎn)品的總銷售額。
在嚴格的SQL里，GROUP BY只能對源表的列進行分組，但PostgreSQL把這 個擴展為既允許GROUP BY對選擇列表中的字段進行分組，也允許對值表達式進行分組，而不僅僅是簡單的字段。
如果一個表已經(jīng)用GROUP BY子句分了組，然后你又只對其中的某些組感興趣， 那么就可以用HAVING子句篩選分組。必須像WHERE子句，從結(jié)果中消除組，語法是
SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression
在HAVING子句中的表達式可以引用分組的表達式和未分組的表達式(后者必須涉及一個聚集函數(shù))。
例子:
=> 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)
然后是一個更現(xiàn)實的例子：
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子句在尚未分組之前根據(jù)s.date字段選擇數(shù)據(jù)行 (表達式只是對那些最近四周發(fā)生的銷售為真)。而HAVING子句在分組之后 選擇那些銷售總額超過5000的組。請注意聚集表達式不需要在查詢中的所有地方都一樣。
如果一個查詢調(diào)用了聚合函數(shù)，但沒有GROUP BY子句，分組仍然發(fā)生： 結(jié)果是單一組行（或者如果單一行被HAVING所淘汰，那么也許沒有行）。 同樣，它包含一個HAVING子句，甚至沒有任何聚合函數(shù)的調(diào)用或GROUP BY子句。
7.2.4. 窗口函數(shù)處理
如果查詢包含窗口函數(shù)(參考Section 3.5，Section 9.19和Section 4.2.8)， 這些函數(shù)在執(zhí)行了分組、聚合和HAVING過濾之后被評估。 也就是說，如果查詢使用任何的聚合、GROUP BY或HAVING，那么 由窗口函數(shù)發(fā)現(xiàn)的行是該組行而不是從FROM/WHERE得到的原始表行。
當多個窗口函數(shù)被使用的時候，所有的窗口函數(shù)依照語法地等效于在它們的窗口定義被單一數(shù)據(jù) 所評估的PARTITION BY和ORDER BY子句中。 因此它們將看到同樣的排序，即使ORDER BY不唯一確定一個排序。 然而，不確保所做出的關(guān)于評價的功能有不同的PARTITION BY或ORDER BY 規(guī)范。（在這種情況下，一個排序步驟通常需要在窗口函數(shù)評價和排序不被保證保存看似跟ORDER BY等效的行命令。）
目前，窗口函數(shù)總是需要分類數(shù)據(jù)，所以查詢輸出將按照一個或另一個窗口函數(shù)PARTITION BY/ORDER BY子句。它不是說依賴于此。 如果你想要確保結(jié)果是按特定的方式分類那么使用顯式的頂級ORDER BY子句。