在 PostgreSQL 中使用連線來組合來自不同表的資料

內連線

預設的連線稱為內連線（inner join）。在 PostgreSQL 中，這可以透過使用INNER JOIN或簡單地使用JOIN來指定。

下面是一個演示內連線語法的典型示例：

SELECT
    *
FROM
    table_1
[INNER] JOIN table_2
    ON table_1.id = table_2.table_1_id;

內連線是最嚴格的連線型別，因為它只顯示由每個表中的行組合建立的行。構成表中任何在另一個表中沒有匹配對應項的行都將從結果中刪除。例如，如果第一個表在比較列中有一個值“blue”，而第二個表中沒有具有該值的記錄，則該行將從輸出中刪除。

如果您將結果表示為元件表的維恩圖，內連線允許您表示兩個圓的重疊區域。只存在於其中一個表中的值都不會顯示。

左連線

左連線是一種連線，它顯示內連線中找到的所有記錄，以及來自第一個表的所有不匹配行。在 PostgreSQL 中，這可以指定為LEFT OUTER JOIN或僅為LEFT JOIN。

左連線的基本語法遵循以下模式：

SELECT
    *
FROM
    table_1
LEFT JOIN table_2
    ON table_1.id = table_2.table_1_id;

左連線首先執行內連線以從兩個表中所有匹配的記錄構建行。然後，第一個表中不匹配的記錄也會被包含進來。由於連線中的每一行都包含兩個表的列，不匹配的列將使用NULL作為第二個表中所有列的值。

如果您將結果表示為元件表的維恩圖，左連線允許您表示整個左圓。左圓中由兩個圓的交集表示的部分將有右表補充的額外資料。

右連線

右連線是一種連線，它顯示內連線中找到的所有記錄，以及來自第二個表的所有不匹配行。在 PostgreSQL 中，這可以指定為RIGHT OUTER JOIN或僅為RIGHT JOIN。

右連線的基本語法遵循以下模式：

SELECT
    *
FROM
    table_1
RIGHT JOIN table_2
    ON table_1.id = table_2.table_1_id;

右連線首先執行內連線以從兩個表中所有匹配的記錄構建行。然後，第二個表中不匹配的記錄也會被包含進來。由於連線中的每一行都包含兩個表的列，不匹配的列將使用NULL作為第一個表中所有列的值。

如果您將結果表示為元件表的維恩圖，右連線允許您表示整個右圓。右圓中由兩個圓的交集表示的部分將有左表補充的額外資料。

全連線

全連線是一種連線，它顯示內連線中找到的所有記錄，以及來自兩個元件表的所有不匹配行。在 PostgreSQL 中，這可以指定為FULL OUTER JOIN或僅為FULL JOIN。

全連線的基本語法遵循以下模式：

SELECT
    *
FROM
    table_1
FULL JOIN table_2
    ON table_1.id = table_2.table_1_id;

全連線首先執行內連線以從兩個表中所有匹配的記錄構建行。然後，兩個表中不匹配的記錄也會被包含進來。由於連線中的每一行都包含兩個表的列，不匹配的列將使用NULL作為不匹配的另一個表中所有列的值。

如果您將結果表示為元件表的維恩圖，全連線允許您完全表示兩個元件圓。兩個圓的交集將包含由每個元件表提供的值。圓中超出重疊區域的部分將包含它們所屬表的值，並使用NULL填充另一個表中找到的列。

交叉連線

還提供了一種特殊的連線，稱為CROSS JOIN。交叉連線不使用任何比較來確定每個表中的行是否相互匹配。相反，結果是透過簡單地將第一個表中的每一行新增到第二個表中的每一行來構造的。

這會生成兩個或多個表中行的笛卡爾積。實際上，這種連線方式無條件地組合了每個表中的行。因此，如果每個表有三行，則結果表將有九行，包含兩個表中的所有列。

例如，如果您有一個名為t1的表與一個名為t2的表組合，每個表都有行r1、r2和r3，則結果將是九行，組合如下：

t1.r1 + t2.r1
t1.r1 + t2.r2
t1.r1 + t2.r3
t1.r2 + t2.r1
t1.r2 + t2.r2
t1.r2 + t2.r3
t1.r3 + t2.r1
t1.r3 + t2.r2
t1.r3 + t2.r3

自連線

自連線是任何將表的行與其自身結合的連線。這可能不會立即顯現其用途，但它實際上有許多常見的應用。

通常，表描述了可以相互扮演多種角色的實體。例如，如果您有一個people表，每行可能包含一個mother列，該列引用表中的其他people。自連線將允許您透過將表的第二個例項連線到第一個例項來拼接這些不同的行，其中這些值匹配。

由於自連線兩次引用同一個表，因此需要表別名來消除引用歧義。例如，在上面的示例中，您可以使用別名people AS children和people AS mothers來連線people表的兩個例項。這樣，您在定義連線條件時就可以指定所引用的表例項。

這是另一個例子，這次代表員工和經理之間的關係：

SELECT
    *
FROM
    people AS employee
JOIN people AS manager
    ON employee.manager_id = manager.id;

ON 子句

定義表連線條件最標準的方法是使用ON子句。ON子句使用等號來指定將要比較的每個表中的精確列，以確定何時可以發生連線。PostgreSQL 使用提供的列來拼接每個表中的行。

ON子句是最冗長的，但也是可用連線條件中最靈活的。它允許無論組合的每個表的列名標準化程度如何，都能保持特異性。

ON 子句的基本語法如下：

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.ident;

在這裡，當table1的id列與table2的ident列匹配時，table1和table2的行將被連線。由於使用了內連線，結果只會顯示那些已連線的行。由於查詢使用了萬用字元*字元，因此將顯示兩個表中的所有列。

這意味著table1的id列和table2的ident列都將顯示，儘管它們透過滿足連線條件而具有完全相同的值。您可以透過在SELECT列列表中明確指定要顯示的列來避免這種重複。

USING 子句

USING子句是指定ON子句條件的簡寫，當被比較的列在兩個表中具有相同的名稱時可以使用。USING子句接受一個用括號括起來的列表，其中包含應被比較的共享列名。

USING子句的一般語法採用這種格式：

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state);

當兩個表共享的兩個列（id和state）的值都匹配時，此連線將table1與table2組合。

同樣的連線可以用ON更冗長地表達如下：

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state;

雖然上述兩種連線都會產生相同的資料，但它們的顯示方式略有不同。雖然ON子句包含兩個表中的所有列，但USING子句會抑制重複的列。因此，結果中將只有共享列的單個例項（而不是每個表有兩個單獨的id列和兩個單獨的state列），然後是table1和table2提供的所有其他列。

NATURAL 子句

NATURAL子句是另一種簡寫，可以進一步減少USING子句的冗長。一個NATURAL連線不指定任何要匹配的列。相反，PostgreSQL 會根據每個資料庫中所有具有匹配列的列自動連線表。

NATURAL連線子句的一般語法如下：

SELECT
    *
FROM
    table1
NATURAL JOIN
    table2;

假設table1和table2都具有名為id、state和company的列，則上述查詢等效於使用ON子句的此查詢：

SELECT
    *
FROM
    table1
JOIN
    table2
ON
    table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company;

以及使用USING子句的此查詢：

SELECT
    *
FROM
    table1
JOIN
    table2
USING
    (id, state, company);

與USING子句一樣，NATURAL子句會抑制重複列，因此結果中每個連線的列只有一個例項。

雖然NATURAL子句可以減少查詢的冗長，但在使用它時必須小心。由於用於連線表的列是自動計算的，如果元件表中的列發生變化，結果可能會因新的連線條件而大相徑庭。