PostgreSQL 连接(JOIN)

PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

在 PostgreSQL 中，JOIN 有五种连接类型：

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

接下来让我们创建两张表 COMPANY 和 DEPARTMENT 。

实例

创建 COMPANY 表（下载 COMPANY SQL 文件 ），数据内容如下：

									yssmxdb# 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），如下所示：

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

得到结果如下：

									yssmxdb=# 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;
								

基于上面的表，我们可以写一个内连接，如下所示：

									yssmxdb=# 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 ...
								

基于上面两张表，我们可以写个左外连接，如下：

									yssmxdb=# 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 ...
								

基于上面两张表，我们建立一个右外连接：

									yssmxdb=# 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 ...
								

基于上面两张表，可以建立一个外连接：

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