Postgresql 简明教程

PostgreSQL - JOINS

PostgreSQL Joins 子句用于在一个数据库中组合来自两个或多个表的记录。JOIN 是使用两表中常见值合并两表中的字段的方法。

The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

PostgreSQL 中的 JOIN 类型 -

Join Types in PostgreSQL are −

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

在我们继续之前，让我们考虑两张表，COMPANY 和 DEPARTMENT。我们已经看到用于填充 COMPANY 表的 INSERT 语句。因此，让我们假设 COMPANY 表中可用的记录列表 -

Before we proceed, let us consider two tables, COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let us assume the list of records available in COMPANY table −

 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 有以下定义 -

Another table is DEPARTMENT, has the following definition −

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

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

Here is the list of INSERT statements to populate DEPARTMENT table −

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 表中拥有以下记录列表 −

Finally, we have the following list of records available in DEPARTMENT table −

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

The CROSS JOIN

CROSS JOIN 将第一张表中的每一行与第二张表中的每一行匹配。如果输入表分别有 x 和 y 列，则结果表将有 x+y 列。由于 CROSS JOIN 有可能生成特别大的表，因此必须谨慎仅在适当的时候使用它们。

A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to use them only when appropriate.

以下是 CROSS JOIN 的语法 -

The following is the syntax of CROSS JOIN −

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

基于上述表格，我们可以编写如下 CROSS JOIN -

Based on the above tables, we can write a CROSS JOIN as follows −

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

以上给出的查询将产生以下结果 -

The above given query will produce the following result −

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

The INNER JOIN

INNER JOIN 通过基于连接谓词合并两张表（表 1 和表 2）的列值创建一个新的结果表。该查询将表 1 的每一行与表 2 的每一行进行比较，以找到所有满足连接谓词的行对。当满足连接谓词时，表 1 和表 2 的每一匹配行对的列值都会合并到一个结果行中。

A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row.

INNER JOIN 是最常见的连接类型，并且是默认连接类型。你可以选择性地使用 INNER 关键词。

An INNER JOIN is the most common type of join and is the default type of join. You can use INNER keyword optionally.

以下是 INNER JOIN 的语法 -

The following is the syntax of INNER JOIN −

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

基于上述表格，我们可以编写如下 INNER JOIN -

Based on the above tables, we can write an INNER JOIN as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

以上给出的查询将产生以下结果 -

The above given query will produce the following result −

 emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

The LEFT OUTER JOIN

OUTER JOIN 是 INNER JOIN 的扩展。SQL 标准定义了三种类型的 OUTER JOIN：LEFT、RIGHT 和 FULL，PostgreSQL 支持所有这些。

The OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these.

对于 LEFT OUTER JOIN，首先执行一次 inner join。然后，对于表 T1 中不满足与表 T2 中任何行的连接条件的每一行，都会添加一个连接行，其中 T2 的列包含空值。因此，连接表始终对于 T1 中的每一行至少有一行。

In case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

以下是 LEFT OUTER JOIN 的语法 -

The following is the syntax of LEFT OUTER JOIN −

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

根据上面的表格，我们可以按照如下方式编写内部连接 −

Based on the above tables, we can write an inner join as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

以上给出的查询将产生以下结果 -

The above given query will produce the following result −

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

The RIGHT OUTER JOIN

首先，执行内部连接。然后，对于表 T2 中不满足与表 T1 中的任何行连接条件的每行，使用 T1 列中的空值添加已连接行。这是左连接的相反方式；结果表将始终包含 T2 中每行的行。

First, an inner join is performed. Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in columns of T1. This is the converse of a left join; the result table will always have a row for each row in T2.

以下是 RIGHT OUTER JOIN 的语法 −

The following is the syntax of RIGHT OUTER JOIN −

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

根据上面的表格，我们可以按照如下方式编写内部连接 −

Based on the above tables, we can write an inner join as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

以上给出的查询将产生以下结果 -

The above given query will produce the following result −

 emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

The FULL OUTER JOIN

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

First, an inner join is performed. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. In addition, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.

以下是 FULL OUTER JOIN 的语法 −

The following is the syntax of FULL OUTER JOIN −

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

根据上面的表格，我们可以按照如下方式编写内部连接 −

Based on the above tables, we can write an inner join as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

以上给出的查询将产生以下结果 -

The above given query will produce the following result −

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