Postgresql 简明教程
PostgreSQL - JOINS
PostgreSQL Joins 子句用于在一个数据库中组合来自两个或多个表的记录。JOIN 是使用两表中常见值合并两表中的字段的方法。
PostgreSQL 中的 JOIN 类型 -
-
The CROSS JOIN
-
The INNER JOIN
-
The LEFT OUTER JOIN
-
The RIGHT OUTER JOIN
-
The 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
The CROSS JOIN
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
The INNER JOIN
INNER JOIN 通过基于连接谓词合并两张表(表 1 和表 2)的列值创建一个新的结果表。该查询将表 1 的每一行与表 2 的每一行进行比较,以找到所有满足连接谓词的行对。当满足连接谓词时,表 1 和表 2 的每一匹配行对的列值都会合并到一个结果行中。
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
The LEFT OUTER JOIN
OUTER JOIN 是 INNER JOIN 的扩展。SQL 标准定义了三种类型的 OUTER JOIN:LEFT、RIGHT 和 FULL,PostgreSQL 支持所有这些。
对于 LEFT OUTER JOIN,首先执行一次 inner join。然后,对于表 T1 中不满足与表 T2 中任何行的连接条件的每一行,都会添加一个连接行,其中 T2 的列包含空值。因此,连接表始终对于 T1 中的每一行至少有一行。
以下是 LEFT OUTER JOIN 的语法 -
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 |
The RIGHT OUTER JOIN
首先,执行内部连接。然后,对于表 T2 中不满足与表 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
The FULL OUTER JOIN
首先,执行内部连接。然后,对于表 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 |