postgresql 多表联查

使用语句的先后顺序并不是优先级的排序：

连接分为：内连接和外连接，外连接分为左外连接，右外连接，全连接

概念上解释，表之间联合后数据如何整合。

返回的数据条数，可以通过集合求算。假如A集合有10条数据，B集合有8条数据，交集的有3条数据。

左外连接：左表的数据全部保留，右表中如果没有满足条件的用null对应。返回数据条数假如A是左表，B是右表，那么返回10条。

内连接：两个表连接条件都存在数据保留，相当于数据集合的交集。返回数据条数假如A是左表，B是右表，那么返回3条。

右外连接：右表数据全部保留，左表中如果没有满足条件的用null对应。返回数据条数假如A是左表，B是右表，那么返回8条。

全连接：左表右表数据全部保留。返回数据条数假如A是左表，B是右表，那么返回13条。

语句：

内连接： table A inner join table B on（条件）/ table A inner join table B using（连接字段名称在两个表中相同）。

左外连接：table A left join table B on（条件）/table A left join table B on（连接字段名称在两个表中相同）。

右外连接：table A right join table B on（条件）/table A right join table B on（连接字段名称在两个表中相同）。

全连接（全外连接）：table A full join table B on（条件）/table A full join table B on（连接字段名称在两个表中相同）。

测试实例：

测试表：

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)
);

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

二.内连接

INNER JOIN，其中INNER可以省略。

语法：

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

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

A INNER JOIN B USING（a = b）

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

 

示例:查询选课情况

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)

 

三.左外连接

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

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

语法：

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

A LEFT JOIN B USING(a)

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

 

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

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)

 

四.右外连接

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

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

语法：

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

A RIGHT JOIN B USING(a)

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

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

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

 

五.全外连接

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

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

语法：

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

A FULL OUTTER JOIN B USING(a)

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

 

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

test=# select * from tbl_student full join tbl_student_course using(stu_id) full 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
         3 |   NULL | NULL     | 大学物理
         1 |   NULL | NULL     | 高等数学
(7 rows)

 

查询只在左表存在的数据

 

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

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外，也可以使用这种查询方式作为替代方案。

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

 

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

test=# select * from tbl_student_course right join tbl_course using(course_id) where tbl_student_course.course_id is null;
 course_id | stu_id | course_name 
-----------+--------+-------------
         1 |   NULL | 高等数学
         3 |   NULL | 大学物理
(2 rows)

 

 

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

 

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

test=# select * from tbl_student full join tbl_student_course using(stu_id) full join tbl_course using(course_id)  where tbl_student.stu_id is null or tbl_course.course_id is null; 
 course_id | stu_id | stu_name | course_name 
-----------+--------+----------+-------------
      NULL |      4 | 麻子     | NULL
         3 |   NULL | NULL     | 大学物理
         1 |   NULL | NULL     | 高等数学
(3 rows)

 

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