PostgreSQL JOIN 多表查询

àì夳堔傛蜴生んèń 2022-12-06 04:24 625阅读 0赞

JOIN用于多张表的关联查询，如SELECT子句(SELECT A.a,A.b,B.a,B.d)中既有表A的字段，同时还有B表的字段，此时使用单独使用FROM A或FROM B已经解决不了问题了，使用JOIN来关联表A和表B即可解决问题，即FROM A,B或A JOIN B，这种方式得到的结果其实是表A与表B的笛卡尔积，即如果A是M1行，N1列的表，B是M2行，N2列的表，A JOIN B的结果是(M1\*M2)行，(N1+N2)列的结果集。JOIN可以是等值连接（A JOIN B ON (A.a=B.c)），也可以是非等值连接（即大于，小于等，如A JOIN B ON（A.a > B.c）），也可以不使用任何条件JOIN，即笛卡尔积，但是非等值连接和笛卡尔积实际意义不大，特别是还存在性能问题。

测试表：

create table tbl_course(
        course_id bigint not null primary key,
        course_name varchar(12) not null
    );
    
    create table tbl_student(
        stu_id bigint not null,
        stu_name varchar(12),
        constraint pk_tbl_student_stu_id primary key(stu_id)
    );
    
    create table tbl_student_course(
        stu_id bigint not null,
        course_id bigint not null,
        constraint pk_tbl_student_course_stu_id_course_id primary key(stu_id,course_id),
        constraint fk_tbl_student_course_stu_id foreign key(stu_id) references tbl_student(stu_id) ,
        constraint fk_tbl_student_course_course_id foreign key(course_id) references tbl_course(course_id)
    );

test=# insert into tbl_course values(1,'高等数学'),(2,'大学英语'),(3,'大学物理'),(4,'电影欣赏');
    INSERT 0 4
    test=# insert into tbl_student values(1,'张三'),(2,'李四'),(3,'王五'),(4,'麻子');
    INSERT 0 4
    test=# insert into tbl_student_course values (1,2),(1,4),(2,4),(3,4);
    INSERT 0 4

# 一.笛卡尔积 #

示例.笛卡尔积确实没什么实际意义，测试表中其实只有4个选课数据。

test=# select * from tbl_course ,tbl_student,tbl_student_course;
     course_id | course_name | stu_id | stu_name | stu_id | course_id 
    -----------+-------------+--------+----------+--------+-----------
             1 | 高等数学    |      1 | 张三     |      1 |         2
             1 | 高等数学    |      1 | 张三     |      1 |         4
             1 | 高等数学    |      1 | 张三     |      2 |         4
             1 | 高等数学    |      1 | 张三     |      3 |         4
             1 | 高等数学    |      2 | 李四     |      1 |         2
             1 | 高等数学    |      2 | 李四     |      1 |         4
             1 | 高等数学    |      2 | 李四     |      2 |         4
             1 | 高等数学    |      2 | 李四     |      3 |         4
             1 | 高等数学    |      3 | 王五     |      1 |         2
             1 | 高等数学    |      3 | 王五     |      1 |         4
             1 | 高等数学    |      3 | 王五     |      2 |         4
             1 | 高等数学    |      3 | 王五     |      3 |         4
             1 | 高等数学    |      4 | 麻子     |      1 |         2
             1 | 高等数学    |      4 | 麻子     |      1 |         4
             1 | 高等数学    |      4 | 麻子     |      2 |         4
             1 | 高等数学    |      4 | 麻子     |      3 |         4
             2 | 大学英语    |      1 | 张三     |      1 |         2
             2 | 大学英语    |      1 | 张三     |      1 |         4
             2 | 大学英语    |      1 | 张三     |      2 |         4
             2 | 大学英语    |      1 | 张三     |      3 |         4
             2 | 大学英语    |      2 | 李四     |      1 |         2
             2 | 大学英语    |      2 | 李四     |      1 |         4
             2 | 大学英语    |      2 | 李四     |      2 |         4
             2 | 大学英语    |      2 | 李四     |      3 |         4
             2 | 大学英语    |      3 | 王五     |      1 |         2
             2 | 大学英语    |      3 | 王五     |      1 |         4
             2 | 大学英语    |      3 | 王五     |      2 |         4
             2 | 大学英语    |      3 | 王五     |      3 |         4
             2 | 大学英语    |      4 | 麻子     |      1 |         2
             2 | 大学英语    |      4 | 麻子     |      1 |         4
             2 | 大学英语    |      4 | 麻子     |      2 |         4
             2 | 大学英语    |      4 | 麻子     |      3 |         4
             3 | 大学物理    |      1 | 张三     |      1 |         2
             3 | 大学物理    |      1 | 张三     |      1 |         4
             3 | 大学物理    |      1 | 张三     |      2 |         4
             3 | 大学物理    |      1 | 张三     |      3 |         4
             3 | 大学物理    |      2 | 李四     |      1 |         2
             3 | 大学物理    |      2 | 李四     |      1 |         4
             3 | 大学物理    |      2 | 李四     |      2 |         4
             3 | 大学物理    |      2 | 李四     |      3 |         4
             3 | 大学物理    |      3 | 王五     |      1 |         2
             3 | 大学物理    |      3 | 王五     |      1 |         4
             3 | 大学物理    |      3 | 王五     |      2 |         4
             3 | 大学物理    |      3 | 王五     |      3 |         4
             3 | 大学物理    |      4 | 麻子     |      1 |         2
             3 | 大学物理    |      4 | 麻子     |      1 |         4
             3 | 大学物理    |      4 | 麻子     |      2 |         4
             3 | 大学物理    |      4 | 麻子     |      3 |         4
             4 | 电影欣赏    |      1 | 张三     |      1 |         2
             4 | 电影欣赏    |      1 | 张三     |      1 |         4
             4 | 电影欣赏    |      1 | 张三     |      2 |         4
             4 | 电影欣赏    |      1 | 张三     |      3 |         4
             4 | 电影欣赏    |      2 | 李四     |      1 |         2
             4 | 电影欣赏    |      2 | 李四     |      1 |         4
             4 | 电影欣赏    |      2 | 李四     |      2 |         4
             4 | 电影欣赏    |      2 | 李四     |      3 |         4
             4 | 电影欣赏    |      3 | 王五     |      1 |         2
             4 | 电影欣赏    |      3 | 王五     |      1 |         4
             4 | 电影欣赏    |      3 | 王五     |      2 |         4
             4 | 电影欣赏    |      3 | 王五     |      3 |         4
             4 | 电影欣赏    |      4 | 麻子     |      1 |         2
             4 | 电影欣赏    |      4 | 麻子     |      1 |         4
             4 | 电影欣赏    |      4 | 麻子     |      2 |         4
             4 | 电影欣赏    |      4 | 麻子     |      3 |         4
    (64 rows)

JOIN连接分为内连接和外连接，而外连接又分为左外连接，右外连接，全外连接。

# 二.内连接 #

INNER JOIN，其中INNER可以省略。

语法：

A INNER JOIN B ON （A.a = B.b）

如果ON条件中两张表的字段名称相同，还可以简单一点

A INNER JOIN B USING（a = b）

内连接的结果如下图中红色部分

![6b97f529dfd69c551bd6dabd0c75fa91.png][]

示例:查询选课情况

test=# select * from tbl_student_course join tbl_student using(stu_id) join tbl_course using(course_id);
     course_id | stu_id | stu_name | course_name 
    -----------+--------+----------+-------------
             2 |      1 | 张三     | 大学英语
             4 |      1 | 张三     | 电影欣赏
             4 |      2 | 李四     | 电影欣赏
             4 |      3 | 王五     | 电影欣赏
    (4 rows)

# 三.左外连接(LEFT JOIN / LEFT OUTER JOIN) #

左外连接其实是一个内连接然后加上左表独有的数据行，结果集中右表的字段自动补充NULL。

LEFT OUTER JOIN ，其中OUTER可以省略。

语法：

A LEFT JOIN B ON （A.a=B.b）

A LEFT JOIN B USING(a)

左外连接的结果如下图红色部分

![28adbb7489990f07876a4c64b67a7c2a.png][]

示例：查询所有学生的选课信息，包括没选课的学生

test=# select * from tbl_student left join tbl_student_course using(stu_id) left join tbl_course using(course_id);
     course_id | stu_id | stu_name | course_name 
    -----------+--------+----------+-------------
             2 |      1 | 张三     | 大学英语
             4 |      1 | 张三     | 电影欣赏
             4 |      2 | 李四     | 电影欣赏
             4 |      3 | 王五     | 电影欣赏
          NULL |      4 | 麻子     | NULL
    (5 rows)

# 四.右外连接(RIGHT JOIN / RIGHT OUTER JOIN) #

右外连接其实是一个内连接然后加上右表独有的数据行，结果集中左表的字段自动补充NULL。

RIGHT OUTER JOIN ，其中OUTER可以省略。

语法：

A RIGHT JOIN B ON （A.a=B.b）

A RIGHT JOIN B USING(a)

右外连接的结果如下图红色部分

![c61d261a5b1a4ae75de142ad7c85e781.png][]

示例：查询所有课程被选情况

# 五.全外连接(FULL JOIN / FULL OUTER JOIN) #

全外连接其实是一个内连接然后加上左表和右表独有的数据行，左表独有的数据行右表的字段补充NULL，右表独有的数据行左表字段补充NULL。

FULL OUTER JOIN，其中OUTER可以省略。

语法：

A FULL OUTER JOIN B ON (A.a = B.b)

A FULL OUTER JOIN B USING(a)

全外连接的结果如下图红色部分

![48b3ab046f4d8071264cdee5390027eb.png][]

示例：查询所有学生和课程的选课信息

查询只在左表存在的数据

![c29608417b697f4844e5742c35223d8e.png][]

示例：查询没有选课的学生

test=# select * from tbl_student left join tbl_student_course using(stu_id) where tbl_student_course.stu_id is null;
     stu_id | stu_name | course_id 
    --------+----------+-----------
          4 | 麻子     |      NULL
    (1 row)

NOT IN存在很大的性能瓶颈，除NOT EXISTS外，也可以使用这种查询方式作为替代方案。

查询只在右表中存在的数据

![3027757104ca14b368ceaef016fbc158.png][]

示例：查询没有被选的课程

查询只在左表或只在右表存在的数据

![41687afcc7f507fcebd5e9e62a598da0.png][]

示例：查询没有选课的学生和没有被选的课程

所有的JOIN查询，只要理解了下面的图，一切就OK了！

![deb756323ad702122476ac9bdfe0e246.png][]

原文链接：[http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins][http_www.codeproject.com_Articles_33052_Visual-Representation-of-SQL-Joins]

[6b97f529dfd69c551bd6dabd0c75fa91.png]: /images/20221123/048807c90031431cbaf2ff62616c1682.png
[28adbb7489990f07876a4c64b67a7c2a.png]: /images/20221123/94b0141524964d68a2ca431ced62ceaa.png
[c61d261a5b1a4ae75de142ad7c85e781.png]: /images/20221123/d670a4b65f5e444ab3d57238ae9fb4e1.png
[48b3ab046f4d8071264cdee5390027eb.png]: /images/20221123/87813240e7ea435b93b426f5e154ca64.png
[c29608417b697f4844e5742c35223d8e.png]: /images/20221123/0113a600502543ffbceecd28c7546374.png
[3027757104ca14b368ceaef016fbc158.png]: /images/20221123/d58b8534dc144bfd901e3db9d0e3ba9e.png
[41687afcc7f507fcebd5e9e62a598da0.png]: /images/20221123/cb00b4e8a9b24cddbb2043437ab8ce8d.png
[deb756323ad702122476ac9bdfe0e246.png]: /images/20221123/abb28008bce647aca9046ce163b0d080.png
[http_www.codeproject.com_Articles_33052_Visual-Representation-of-SQL-Joins]: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins