PostgreSQL - 连接 (liánjiē)

---

---

PostgreSQL 的连接 (liánjiē)子句用于组合数据库中两个或多个表中的记录。连接是一种通过使用每个表中共同的值来组合两个表中的字段的方法。

PostgreSQL 中的连接类型包括：

• 交叉连接 (Chāojiā liánjiē)
• 内部连接 (Nèibù liánjiē)
• 左外部连接 (Zuǒ wàibù liánjiē)
• 右外部连接 (Yòu wàibù liánjiē)
• 全外部连接 (Quán wàibù liánjiē)

在我们继续之前，让我们考虑两个表，COMPANY 和 DEPARTMENT。我们已经看过用于填充 COMPANY 表的 INSERT 语句。所以让我们假设 COMPANY 表中存在以下记录：

 id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

另一个表是 DEPARTMENT，其定义如下：

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

以下是用于填充 DEPARTMENT 表的 INSERT 语句列表：

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

交叉连接 (Chāojiā liánjiē)

交叉连接 (CROSS JOIN) 将第一个表的每一行与第二个表的每一行匹配。如果输入表分别具有 x 和 y 列，则结果表将具有 x+y 列。由于交叉连接 (CROSS JOIN) 可能会生成非常大的表，因此必须注意仅在适当的时候使用它们。

以下是交叉连接 (CROSS JOIN) 的语法：

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

基于上述表格，我们可以编写一个交叉连接 (CROSS JOIN) 如下：

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

上述查询将产生以下结果：

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

内部连接 (Nèibù liánjiē)

内部连接 (INNER JOIN) 通过基于连接谓词组合两个表（table1 和 table2）的列值来创建一个新的结果表。查询比较 table1 的每一行与 table2 的每一行，以查找满足连接谓词的所有行对。当满足连接谓词时，table1 和 table2 的每一对匹配行的列值将组合到结果行中。

内部连接 (INNER JOIN) 是最常见的连接类型，也是默认的连接类型。您可以选择使用 INNER 关键字。

以下是内部连接 (INNER JOIN) 的语法：

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

基于上述表格，我们可以编写一个内部连接 (INNER JOIN) 如下：

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

左外部连接 (Zuǒ wàibù liánjiē)

外部连接 (OUTER JOIN) 是内部连接 (INNER JOIN) 的扩展。SQL 标准定义了三种类型的外部连接 (OUTER JOIN)：左、右和全，PostgreSQL 支持所有这些。

对于左外部连接 (LEFT OUTER JOIN)，首先执行内部连接 (INNER JOIN)。然后，对于在表 T1 中但不满足与表 T2 中任何行的连接条件的每一行，将添加一个连接行，其中 T2 的列中包含 null 值。因此，连接表始终至少包含 T1 中每一行的一行。

以下是左外部连接 (LEFT OUTER JOIN) 的语法：

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

基于上述表格，我们可以编写一个内部连接 (INNER JOIN) 如下：

testdb=# 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
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

右外部连接 (Yòu wàibù liánjiē)

首先，执行内部连接 (INNER JOIN)。然后，对于在表 T2 中但不满足与表 T1 中任何行的连接条件的每一行，将添加一个连接行，其中 T1 的列中包含 null 值。这是左连接的逆；结果表将始终包含 T2 中每一行的一行。

以下是右外部连接 (RIGHT OUTER JOIN) 的语法：

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

基于上述表格，我们可以编写一个内部连接 (INNER JOIN) 如下：

testdb=# 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 |       | Finance

全外部连接 (Quán wàibù liánjiē)

首先，执行内部连接 (INNER JOIN)。然后，对于在表 T1 中但不满足与表 T2 中任何行的连接条件的每一行，将添加一个连接行，其中 T2 的列中包含 null 值。此外，对于在 T2 中但不满足与表 T1 中任何行的连接条件的每一行，将添加一个在 T1 的列中包含 null 值的连接行。

以下是全外部连接 (FULL OUTER JOIN) 的语法：

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

基于上述表格，我们可以编写一个内部连接 (INNER JOIN) 如下：

testdb=# 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 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |