PostgreSQL 表表達(dá)式

一個連接表是根據(jù)特定的連接類型的規(guī)則從兩個其它表（真實表或生成表）中派生的表。目前支持內(nèi)連接、外連接和交叉連接。一個連接表的一般語法是：

T1 join_type T2 [ join_condition ]

所有類型的連接都可以被鏈在一起或者嵌套：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)的條件。這種功能可能對某些查詢很有用，但是需要我們仔細(xì)想清楚。例如：

=> 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子句中的一個約束是在連接之后被處理。這對內(nèi)連接沒有關(guān)系，但是對于外連接會帶來麻煩。

你可以給一個表或復(fù)雜的表引用指定一個臨時的名字，用于剩下的查詢中引用那些派生的表。這被叫做表別名。

要創(chuàng)建一個表別名，我們可以寫：

FROM table_reference AS alias

或者

FROM table_reference alias

AS關(guān)鍵字是可選的。別名可以是任意標(biāo)識符。

表別名的典型應(yīng)用是給長表名賦予比較短的標(biāo)識符， 好讓連接子句更易讀。例如：

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

到這里，別名成為當(dāng)前查詢的表引用的新名稱 — 我們不再能夠用該表最初的名字引用它了。因此，下面的用法是不合法的：

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

表別名主要用于簡化符號，但是當(dāng)把一個表連接到它自身時必須使用別名，例如：

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

此外，如果一個表引用是一個子查詢，則必須要使用一個別名（見第 7.2.1.3子查詢 ）。

圓括弧用于解決歧義。在下面的例子中，第一個語句將把別名b賦給my_table的第二個實例，但是第二個語句把別名賦給連接的結(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子句的輸出附加了一個別名， 那么該別名就在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.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列表中的列分配別名是可選的，但是選擇這樣做是一個好習(xí)慣。更多信息可參見第 7.7 節(jié)。

表函數(shù)是那些生成一個行集合的函數(shù)，這個集合可以是由基本數(shù)據(jù)類型（標(biāo)量類型）組成， 也可以是由復(fù)合數(shù)據(jù)類型（表行）組成。它們的用法類似一個表、視圖或者在查詢的FROM子句里的子查詢。表函數(shù)返回的列可以像一個表列、視圖或者子查詢那樣被包含在SELECT、JOIN或WHERE子句里。

也可以使用ROWS FROM語法將平行列返回的結(jié)果組合成表函數(shù)； 這種情況下結(jié)果行的數(shù)量是最大一個函數(shù)結(jié)果的數(shù)量，較小的結(jié)果會用空值來填充。

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

如果指定了WITH ORDINALITY子句，一個額外的 bigint類型的列將會被增加到函數(shù)的結(jié)果列中。這個列對 函數(shù)結(jié)果集的行進(jìn)行編號，編號從 1 開始（這是對 SQL 標(biāo)準(zhǔn)語法 UNNEST ... WITH ORDINALITY的一般化）。默認(rèn)情 況下，序數(shù)列被稱為ordinality，但也可以通過使用一個 AS子句給它分配一個不同的列名。

調(diào)用特殊的表函數(shù)UNNEST可以使用任意數(shù)量的數(shù)組參數(shù)， 它會返回對應(yīng)的列數(shù)，就好像在每一個參數(shù)上單獨調(diào)用 UNNEST（第 9.19 節(jié)）并且使用 ROWS FROM結(jié)構(gòu)把它們組合起來。

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

如果沒有指定table_alias，該函數(shù)名將被用作 表名。在ROWS FROM()結(jié)構(gòu)的情況中，會使用第一個函數(shù)名。

如果沒有提供列的別名，那么對于一個返回基數(shù)據(jù)類型的函數(shù)，列名也與該函數(shù) 名相同。對于一個返回組合類型的函數(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ù)是很有用的。 為了支持這些，表函數(shù)可以被聲明為返回沒有OUT參數(shù)的偽類型record。 如果在查詢里使用這樣的函數(shù)，那么我們必須在查詢中指定所預(yù)期的行結(jié)構(gòu)，這樣系統(tǒng)才知道如何分析和規(guī)劃該查詢。 這種語法是這樣的：

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()語法時， 可以為每一個成員函數(shù)單獨附著一個 column_definition列表；或者在只有一個成員 函數(shù)并且沒有WITH ORDINALITY子句的情況下，可以在 ROWS FROM()后面寫一個 column_definition列表來取代一個列別名列表。

考慮下面的例子：

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

dblink函數(shù)（dblink模塊的一部分）執(zhí)行一個遠(yuǎn)程的查詢。它被聲明為返回record，因為它可能會被用于任何類型的查詢。 實際的列集必須在調(diào)用它的查詢中指定，這樣分析器才知道類似*這樣的東西應(yīng)該擴展成什么樣子。

此示例使用ROWS FROM：

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

  p  |  q  | s
-----+-----+---
  40 | foo | 1
 100 | bar | 2
     |     | 3

它將兩個函數(shù)連接到一個FROM目標(biāo)中。 json_to_recordset()被指示返回兩列，第一個integer 和第二個text。generate_series()的結(jié)果直接使用。 ORDER BY子句將列值排序為整數(shù)。

可以在出現(xiàn)于FROM中的子查詢前放置關(guān)鍵詞LATERAL。這允許它們引用前面的FROM項提供的列（如果沒有LATERAL，每一個子查詢將被獨立計算，并且因此不能被其他FROM項交叉引用）。

出現(xiàn)在FROM中的表函數(shù)的前面也可以被放上關(guān)鍵詞LATERAL，但對于函數(shù)該關(guān)鍵詞是可選的，在任何情況下函數(shù)的參數(shù)都可以包含對前面的FROM項提供的列的引用。

一個LATERAL項可以出現(xiàn)在FROM列表頂層，或者出現(xiàn)在一個JOIN樹中。在后一種情況下，如果它出現(xiàn)在JOIN的右部，那么它也可以引用 在JOIN左部的任何項。

如果一個FROM項包含LATERAL交叉引用，計算過程如下：對于提供交叉引用列的FROM項的每一行，或者多個提供這些列的多個FROM項的行集合，LATERAL項將被使用該行或者行集中的列值進(jìn)行計算。得到的結(jié)果行將和它們被計算出來的行進(jìn)行正常的連接。對于來自這些列的源表的每一行或行集，該過程將重復(fù)。

LATERAL的一個簡單例子：

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

這不是非常有用，因為它和一種更簡單的形式得到的結(jié)果完全一樣：

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

在必須要使用交叉引用列來計算那些即將要被連接的行時，LATERAL是最有用的。一種常用的應(yīng)用是為一個返回集合的函數(shù)提供一個參數(shù)值。例如，假設(shè)vertices(polygon)返回一個多邊形的頂點集合，我們可以這樣標(biāo)識存儲在一個表中的多邊形中靠近的頂點：

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關(guān)鍵詞在這個例子中并不是必不可少的，但是我們在這里使用它是為了使表述更清晰）。

有時候也會很特別地把LEFT JOIN放在一個LATERAL子查詢的前面，這樣即使LATERAL子查詢對源行不產(chǎn)生行，源行也會出現(xiàn)在結(jié)果中。例如，如果get_product_names()返回一個制造商制造的產(chǎn)品的名字，但是某些制造商在我們的表中目前沒有制造產(chǎn)品，我們可以找出哪些制造商是這樣：

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;