PostgreSQL JOINS子句

PostgreSQL 连接子句用于在数据库中合并两个或多个表的记录。JOIN是通过使用每个表中的共同值来合并两个表的字段的一种方法。

PostgreSQL中的连接类型为 −

CROSS JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

在继续之前，让我们考虑两个表，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

交叉连接

交叉连接匹配第一个表的每一行与第二个表的每一行。如果输入表分别有x和y列，结果表将有x+y列。由于交叉连接有可能生成非常大的表，所以必须谨慎使用。

以下是交叉连接的语法：

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

内连接

内连接通过基于连接条件，将两个表(table1和table2)的列值组合在一起，创建一个新的结果表。查询将table1的每一行与table2的每一行进行比较，找到满足连接条件的所有行对。当连接条件满足时，table1和table2的每个匹配行的列值被组合成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，也是默认的连接类型。您可以选择性地使用INNER关键字。

以下是内连接的语法：

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

基于上述表格，我们可以将内连接写成如下形式-

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

左外连接

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

对于左外连接，首先执行内连接。然后，对于表T1中与表T2中的任何行都不满足连接条件的每一行，都会添加一个具有T2列中的空值的连接行。因此，连接表中始终至少存在一行与T1中的每一行对应。

以下是左外连接的语法−

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

根据上述表格，我们可以将内连接写成如下形式：

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 |

RIGHT OUTER JOIN（右外连接）

首先执行内连接（inner join）。然后，对于表T2中的每一行，如果它与表T1中的任何一行不满足连接条件，则在T1的列中添加空值，并与T1进行连接。这是左连接的相反操作；结果表中始终会对于T2中的每一行，有一个与之对应的行。

下面是RIGHT OUTER JOIN的语法−

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

根据上述表格，我们可以将内部连接写成以下格式：

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

全外连接

首先执行内连接。然后，对于表T1中不满足与表T2任何行的连接条件的每一行，都会添加一个带有列T2的空值的连接行。此外，对于表T2中不满足与表T1任何行的连接条件的每一行，都会添加一个带有列T1的空值的连接行。

以下是FULL OUTER JOIN的语法：

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

根据以上表格，我们可以如下编写一个内连接：

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 |