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)