使用 Join 在 PostgreSQL 中合併來自不同表格的資料

Inner Join

預設的 Join 稱為 Inner Join。在 PostgreSQL 中，可以使用 INNER JOIN 或簡稱為 JOIN 來指定。

以下是一個典型的範例，示範 Inner Join 的語法

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

Inner Join 是限制性最強的 Join 類型，因為它僅顯示透過合併每個表格中的列所建立的列。構成表格中任何在另一個表格中沒有匹配項的列都會從結果中移除。例如，如果第一個表格在比較欄位中的值為「藍色」，而第二個表格沒有具有該值的記錄，則該列將從輸出中隱藏。

如果您將結果表示為組件表格的 Venn 圖，則 Inner Join 允許您表示兩個圓圈的重疊區域。僅在其中一個表格中存在的值都不會顯示。

Left Join

Left Join 是一種 Join，它顯示在 Inner Join 中找到的所有記錄，以及來自第一個表格的所有不匹配列。在 PostgreSQL 中，可以將其指定為 LEFT OUTER JOIN 或僅指定為 LEFT JOIN。

Left Join 的基本語法遵循以下模式

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

Left Join 的建構方式是首先執行 Inner Join，以從兩個表格中所有匹配的記錄建構列。之後，也包含來自第一個表格的不匹配記錄。由於 Join 中的每個列都包含兩個表格的欄位，因此不匹配的欄位會使用 NULL 作為第二個表格中所有欄位的值。

如果您將結果表示為組件表格的 Venn 圖，則 Left Join 允許您表示整個左圓圈。由兩個圓圈之間的交集表示的左圓圈部分將具有由右側表格補充的額外資料。

Right Join

Right Join 是一種 Join，它顯示在 Inner Join 中找到的所有記錄，以及來自第二個表格的所有不匹配列。在 PostgreSQL 中，可以將其指定為 RIGHT OUTER JOIN 或僅指定為 RIGHT JOIN。

Right Join 的基本語法遵循以下模式

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

Right Join 的建構方式是首先執行 Inner Join，以從兩個表格中所有匹配的記錄建構列。之後，也包含來自第二個表格的不匹配記錄。由於 Join 中的每個列都包含兩個表格的欄位，因此不匹配的欄位會使用 NULL 作為第一個表格中所有欄位的值。

如果您將結果表示為組件表格的 Venn 圖，則 Right Join 允許您表示整個右圓圈。由兩個圓圈之間的交集表示的右圓圈部分將具有由左側表格補充的額外資料。

Full Join

Full Join 是一種 Join，它顯示在 Inner Join 中找到的所有記錄，以及來自兩個組件表格的所有不匹配列。在 PostgreSQL 中，可以將其指定為 FULL OUTER JOIN 或僅指定為 FULL JOIN。

Full Join 的基本語法遵循以下模式

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

Full Join 的建構方式是首先執行 Inner Join，以從兩個表格中所有匹配的記錄建構列。之後，也包含來自兩個表格的不匹配記錄。由於 Join 中的每個列都包含兩個表格的欄位，因此不匹配的欄位會使用 NULL 作為不匹配的其他表格中所有欄位的值。

如果您將結果表示為組件表格的 Venn 圖，則 Full Join 允許您完整表示兩個組件圓圈。兩個圓圈的交集將具有由每個組件表格提供的值。圓圈中重疊區域外部的部分將具有來自它們所屬表格的值，並使用 NULL 來填寫在另一個表格中找到的欄位。

Cross Join

也提供一種稱為 CROSS JOIN 的特殊 Join。Cross Join 不使用任何比較來確定每個表格中的列是否彼此匹配。相反，結果的建構方式只是將第一個表格中的每個列新增到第二個表格的每個列。

這會產生兩個或多個表格中列的笛卡爾積。實際上，這種樣式的 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

Self Join

Self Join 是任何將表格的列與自身合併的 Join。可能不容易立即看出這有什麼用處，但它實際上具有許多常見的應用。

通常，表格描述了可以履行彼此之間關係中多個角色的實體。例如，如果您有一個 people 表格，則每一列都可能包含一個 mother 欄位，該欄位引用表格中的其他 people。Self Join 將允許您透過將表格的第二個實例與第一個實例連接起來，在這些值匹配時將這些不同的列縫合在一起。

由於 Self Join 兩次引用相同的表格，因此需要表格別名來消除引用的歧義。例如，在上面的範例中，您可以透過使用別名 people AS children 和 people AS mothers 來連接 people 表格的兩個實例。這樣，您就可以在定義 Join 條件時指定您要引用的表格實例。

這是另一個範例，這次表示員工和經理之間的關係

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

ON 子句

定義表格 Join 條件的最標準方法是使用 ON 子句。ON 子句使用等號來指定每個表格中的確切欄位，這些欄位將被比較以確定何時可能發生 Join。PostgreSQL 使用提供的欄位將每個表格中的列縫合在一起。

ON 子句是最冗長的，但也是可用的 Join 條件中最靈活的。無論要合併的每個表格的欄位名稱標準化程度如何，它都允許指定具體性。

ON 子句的基本語法如下所示

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

在這裡，當 table1 的 id 欄位與 table2 的 ident 欄位相符時，table1 和 table2 中的列將被 Join。由於使用了 Inner Join，因此結果將僅顯示 Join 的列。由於查詢使用萬用字元 *，因此將顯示兩個表格的所有欄位。

這表示即使 table1 的 id 欄位和 table2 的 ident 欄位由於滿足 Join 條件而具有完全相同的值，它們也將被顯示。您可以透過在 SELECT 欄位清單中呼叫您希望顯示的確切欄位來避免這種重複。

USING 子句

USING 子句是指定 ON 子句條件的簡寫，當要比較的欄位在兩個表格中都具有相同的名稱時可以使用。USING 子句採用括在括號中的共用欄位名稱清單，應比較這些名稱。

USING 子句的一般語法使用以下格式

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

當兩個表格共用的兩個欄位（id 和 state）都具有匹配的值時，此 Join 會將 table1 與 table2 合併。

可以使用 ON 更詳細地表示相同的 Join，如下所示

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

雖然以上兩個 Join 都會產生相同的列，並且其中存在相同的資料，但它們的顯示方式會略有不同。雖然 ON 子句包含兩個表格的所有欄位，但 USING 子句會隱藏重複的欄位。因此，結果將僅具有每個共用欄位（而不是兩個單獨的 id 欄位和兩個單獨的 state 欄位（每個表格一個））中的一個，然後是 table1 和 table2 提供的所有其他欄位。

NATURAL 子句

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

NATURAL Join 子句的一般語法如下所示

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 子句會隱藏重複的欄位，因此結果中每個 Join 欄位都只有一個實例。

雖然 NATURAL 子句可以減少查詢的冗長，但在使用它時必須謹慎。由於用於 Join 表格的欄位是自動計算的，因此如果組件表格中的欄位發生變更，則由於新的 Join 條件，結果可能會大相徑庭。