SQL JOIN

SQL连接查询详解

最新推荐文章于 2024-08-12 09:23:46 发布

原创最新推荐文章于 2024-08-12 09:23:46 发布 · 307 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

notes 专栏收录该内容

19 篇文章

订阅专栏

本文深入解析了SQL中各种连接查询的使用方法，包括内连接、左外连接、右外连接和全外连接，并通过具体实例展示了如何从不同角度获取数据。同时，还介绍了集合操作如并集、交集和差集的实现方式。

stud:

Course:

stud_crs:

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud inner join stud_crs on stud.sno = stud_crs.sno;

A intersects B:

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud left outer join stud_crs on stud.sno = stud_crs.sno;

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud right outer join stud_crs on stud.sno = stud_crs.sno;

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud full outer join stud_crs on stud.sno = stud_crs.sno;

A unions B:

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud left outer join stud_crs on stud.sno = stud_crs.sno
	where stud_crs.sno is null;

A - B:

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud right outer join stud_crs on stud.sno = stud_crs.sno
	where stud.sno is null;

B - A:

select stud_crs.scno, stud.sno as 学号, stud.sname as 姓名, stud_crs.cno as 课程号, stud_crs.grade as 分数
	from stud full outer join stud_crs on stud.sno = stud_crs.sno
	where stud.sno is null or stud_crs.sno is null;

(A unions B) - (A intersects B):