PostgreSQL 連接(JOIN)

PostgreSQL JOIN 子句用于把來(lái)自兩個(gè)或多個(gè)表的行結(jié)合起來(lái)，基于這些表之間的共同字段。

在 PostgreSQL 中，JOIN 有五種連接類(lèi)型：

• CROSS JOIN ：交叉連接
• INNER JOIN：內(nèi)連接
• LEFT OUTER JOIN：左外連接
• RIGHT OUTER JOIN：右外連接
• FULL OUTER JOIN：全外連接

接下來(lái)讓我們創(chuàng)建兩張表 COMPANY 和 DEPARTMENT。

實(shí)例

創(chuàng)建 COMPANY 表，數(shù)據(jù)內(nèi)容如下：

w3cschooldb# 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)

我們往表里添加幾條數(shù)據(jù)：

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);

此時(shí)，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)

創(chuàng)建一張 DEPARTMENT 表，添加三個(gè)字段：

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 );

此時(shí)，DEPARTMENT 表的記錄如下：

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

交叉連接

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

由于交叉連接（CROSS JOIN）有可能產(chǎn)生非常大的表，使用時(shí)必須謹(jǐn)慎，只在適當(dāng)?shù)臅r(shí)候使用它們。

下面是 CROSS JOIN 的基礎(chǔ)語(yǔ)法：

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

基于上面的表，我們可以寫(xiě)一個(gè)交叉連接（CROSS JOIN），如下所示：

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

得到結(jié)果如下：

w3cschooldb=# 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

內(nèi)連接

內(nèi)連接（INNER JOIN）根據(jù)連接謂詞結(jié)合兩個(gè)表（table1 和 table2）的列值來(lái)創(chuàng)建一個(gè)新的結(jié)果表。查詢會(huì)把 table1 中的每一行與 table2 中的每一行進(jìn)行比較，找到所有滿足連接謂詞的行的匹配對(duì)。

當(dāng)滿足連接謂詞時(shí)，A 和 B 行的每個(gè)匹配對(duì)的列值會(huì)合并成一個(gè)結(jié)果行。

內(nèi)連接（INNER JOIN）是最常見(jiàn)的連接類(lèi)型，是默認(rèn)的連接類(lèi)型。

INNER 關(guān)鍵字是可選的。

下面是內(nèi)連接（INNER JOIN）的語(yǔ)法：

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

基于上面的表，我們可以寫(xiě)一個(gè)內(nèi)連接，如下所示：

w3cschooldb=# 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)

左外連接

外部連接是內(nèi)部連接的擴(kuò)展。SQL 標(biāo)準(zhǔn)定義了三種類(lèi)型的外部連接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有這些。

對(duì)于左外連接，首先執(zhí)行一個(gè)內(nèi)連接。然后，對(duì)于表 T1 中不滿足表 T2 中連接條件的每一行，其中 T2 的列中有 null 值也會(huì)添加一個(gè)連接行。因此，連接的表在 T1 中每一行至少有一行。

下面是左外連接（ LEFT OUTER JOIN ）的基礎(chǔ)語(yǔ)法：

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

基于上面兩張表，我們可以寫(xiě)個(gè)左外連接，如下：

w3cschooldb=# 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)

右外連接

首先，執(zhí)行內(nèi)部連接。然后，對(duì)于表T2中不滿足表T1中連接條件的每一行，其中T1列中的值為空也會(huì)添加一個(gè)連接行。這與左聯(lián)接相反;對(duì)于T2中的每一行，結(jié)果表總是有一行。

下面是右外連接（ RIGHT OUT JOIN）的基本語(yǔ)法：

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

基于上面兩張表，我們建立一個(gè)右外連接：

w3cschooldb=# 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)

外連接

首先，執(zhí)行內(nèi)部連接。然后，對(duì)于表 T1 中不滿足表 T2 中任何行連接條件的每一行，如果 T2 的列中有 null 值也會(huì)添加一個(gè)到結(jié)果中。此外，對(duì)于 T2 中不滿足與 T1 中的任何行連接條件的每一行，將會(huì)添加 T1 列中包含 null 值的到結(jié)果中。

下面是外連接的基本語(yǔ)法：

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

基于上面兩張表，可以建立一個(gè)外連接：

w3cschooldb=# 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)