使用语句的先后顺序并不是优先级的排序:
连接分为:内连接和外连接,外连接分为左外连接,右外连接,全连接
概念上解释,表之间联合后数据如何整合。
返回的数据条数,可以通过集合求算。假如A集合有10条数据,B集合有8条数据,交集的有3条数据。
左外连接:左表的数据全部保留,右表中如果没有满足条件的用null对应。返回数据条数假如A是左表,B是右表,那么返回10条。
内连接:两个表连接条件都存在数据保留,相当于数据集合的交集。返回数据条数假如A是左表,B是右表,那么返回3条。
右外连接:右表数据全部保留,左表中如果没有满足条件的用null对应。返回数据条数假如A是左表,B是右表,那么返回8条。
全连接:左表右表数据全部保留。返回数据条数假如A是左表,B是右表,那么返回13条。
语句:
内连接: table Ainner jointable B on(条件)/ table Ainner jointable 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 tabletbl_course(
course_id bigint not null primary key,
course_name varchar(12) not null);
create tabletbl_student(
stu_id bigint not null,
stu_name varchar(12),
constraint pk_tbl_student_stu_id primary key(stu_id)
);
create tabletbl_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) referencestbl_student(stu_id) ,
constraint fk_tbl_student_course_course_id foreign key(course_id) referencestbl_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) jointbl_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 jointbl_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 jointbl_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 jointbl_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了!