postgresql----JOIN之多表查询
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,'电影欣赏');
INSERT04
test=# insert into tbl_student values(1,'张三'),(2,'李四'),(3,'王五'),(4,'⿇⼦');
INSERT04
test=# insert into tbl_student_course values (1,2),(1,4),(2,4),(3,4);
INSERT04
⼀.笛卡尔积
⽰例.笛卡尔积确实没什么实际意义，测试表中其实只有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）
内连接的结果如下图中红⾊部分
⽰例：查询选课情况
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
join on是什么连接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 urse_id is null;
course_id | stu_id | stu_name | course_name
-----------+--------+----------+-------------
NULL|4|⿇⼦|NULL
3|NULL|NULL|⼤学物理
1|NULL|NULL|⾼等数学
(3 rows)
所有的JOIN查询，只要理解了下⾯的图，⼀切就OK了！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论