PostgreSQL 連接(JOIN)

PostgreSQL JOIN 子句用於把來自兩個或多個表的行結合起來，基於這些表之間的共同字段。

在 PostgreSQL 中，JOIN 有五種連接類型：

• CROSS JOIN ：交叉連接
• INNER JOIN：內連接
• LEFT OUTER JOIN：左外連接
• RIGHT OUTER JOIN：右外連接
• FULL OUTER JOIN：全外連接

接下來讓我們創建兩張表 COMPANY 和 DEPARTMENT。

實例

創建 COMPANY 表（下載 COMPANY SQL 檔 ），數據內容如下：

zaixiandb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

我們往表裏添加幾條數據：

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

此時，COMPANY 表的記錄如下：

 id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

創建一張 DEPARTMENT 表，添加三個字段：

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

向 DEPARTMENT 表插入三條記錄：

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

此時，DEPARTMENT 表的記錄如下：

 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

交叉連接

交叉連接（CROSS JOIN）把第一個表的每一行與第二個表的每一行進行匹配。如果兩個輸入表分別有 x 和 y 行，則結果表有 x*y 行。

由於交叉連接（CROSS JOIN）有可能產生非常大的表，使用時必須謹慎，只在適當的時候使用它們。

下麵是 CROSS JOIN 的基礎語法：

SELECT ... FROM table1 CROSS JOIN table2 ...

基於上面的表，我們可以寫一個交叉連接（CROSS JOIN），如下所示：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

得到結果如下：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
 emp_id | name  |       dept
--------+-------+--------------------
      1 | Paul  | IT Billing
      1 | Allen | IT Billing
      1 | Teddy | IT Billing
      1 | Mark  | IT Billing
      1 | David | IT Billing
      1 | Kim   | IT Billing
      1 | James | IT Billing
      1 | Paul  | IT Billing
      1 | James | IT Billing
      1 | James | IT Billing
      2 | Paul  | Engineering
      2 | Allen | Engineering
      2 | Teddy | Engineering
      2 | Mark  | Engineering
      2 | David | Engineering
      2 | Kim   | Engineering
      2 | James | Engineering
      2 | Paul  | Engineering
      2 | James | Engineering
      2 | James | Engineering
      7 | Paul  | Finance

內連接

內連接（INNER JOIN）根據連接謂詞結合兩個表（table1 和 table2）的列值來創建一個新的結果表。查詢會把 table1 中的每一行與 table2 中的每一行進行比較，找到所有滿足連接謂詞的行的匹配對。

當滿足連接謂詞時，A 和 B 行的每個匹配對的列值會合並成一個結果行。

內連接（INNER JOIN）是最常見的連接類型，是默認的連接類型。

INNER 關鍵字是可選的。

下麵是內連接（INNER JOIN）的語法：

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

基於上面的表，我們可以寫一個內連接，如下所示：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |        dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 rows)

左外連接

外部連接是內部連接的擴展。SQL 標準定義了三種類型的外部連接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有這些。

對於左外連接，首先執行一個內連接。然後，對於表 T1 中不滿足表 T2 中連接條件的每一行，其中 T2 的列中有 null 值也會添加一個連接行。因此，連接的表在 T1 中每一行至少有一行。

下麵是左外連接（ LEFT OUTER JOIN ）的基礎語法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

基於上面兩張表，我們可以寫個左外連接，如下：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |      dept
--------+-------+----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James |
        | David |
        | Paul  |
        | Kim   |
        | Mark  |
        | Teddy |
        | James |
(10 rows)

右外連接

首先，執行內部連接。然後，對於表T2中不滿足表T1中連接條件的每一行，其中T1列中的值為空也會添加一個連接行。這與左聯接相反;對於T2中的每一行，結果表總是有一行。

下麵是右外連接（ RIGHT OUT JOIN）的基本語法：

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

基於上面兩張表，我們建立一個右外連接：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |    dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 rows)

外連接

首先，執行內部連接。然後，對於表 T1 中不滿足表 T2 中任何行連接條件的每一行，如果 T2 的列中有 null 值也會添加一個到結果中。此外，對於 T2 中不滿足與 T1 中的任何行連接條件的每一行，將會添加 T1 列中包含 null 值的到結果中。

下麵是外連接的基本語法：

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

基於上面兩張表，可以建立一個外連接：

zaixiandb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |      dept
--------+-------+-----------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James |
        | David |
        | Paul  |
        | Kim   |
        | Mark  |
        | Teddy |
        | James |
(10 rows)