240429_SQL中的JOIN

连接查询

我最熟悉的多表联立 from 表1,表2,然后where里通过外键把表联立起来。这其实就是inner join.

但现在扩展的SQL可以在from里带上join直接做连接了

select ...
from 表1
	{inner | {left | right | full} [outer]} join 表2
	on 连接条件

连接条件写在join下的on里，如果不带on条件的话，就和from多表where为空，简单的笛卡尔积一样

• inner join

隐式内连接: select 字段列表 from 表1, 表2 where 连接条件;

显式内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件;

不写inner直接写join默认是内连接，内连接查询出来是两表的交集

select *
from Student, Department
where Student.DepartmentID = Department.DepartmentID;
---
select *
from Student join Department on Student.DepartmentID = Department.DepartmentID;

郭靖和周芷若由于DepartmentID为空，所以不在检索结果里

• outer join

select 字段列表 from 表1 left|right [outer] join 表2 on 连接条件;

左外连接完全保留表1的数据，一般连接条件是对于表2做筛选。可以理解为筛选完的拼接到表1的记录上

select *
from Student left join Department on Student.DepartmentID = Department.DepartmentID;

左外连接保留了郭靖，周芷若

右外连接保留了机械，电子

full join的话就全都保留了

---

小红书上看别人的面经帖子，手撕SQL: (一个学生表，一个成绩表，每个学生可能对应多个成绩，选出有一个科以上不及格的同学)

若题意只要有一门不及格的就搜出来：

-- 只搜sid
SELECT DISTINCT sid
FROM SC
WHERE score < 60

-- 联立学生表输出Snmae
SELECT DISTINCT Sname
FROM Student, SC
WHERE Student.sid = Sc.sid and score < 60
-- 使用join
SELECT DISTINCT Sname
FROM Student JOIN SC
ON Student.sid = Sc.sid and score < 60


-- 但以上终归是要做笛卡尔积的，所以说join要少用，性能不好。大厂三表以上禁止join
-- 考虑使用嵌套查询(in)避免做笛卡尔积
SELECT * FROM Student
WHERE sid in (
	SELECT DISTINCT sid
	FROM SC
	WHERE score < 60
)

==尽量多用嵌套查询，避免from多表联立join的连接操作==

若题意是>=2门不及格的： 需要聚合函数count()，以及按sid来分组，涉及到group by, having.

分组查询

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT column_name(s)
FROM table_name
[WHERE condition]
GROUP BY column_name(s)
[HAVING 分组后过滤条件]
[ORDER BY column_name(s)];

where与having的区别:

• 执行时机不同：where是分组之前进行过滤，不满足where条件的不参与分组直接筛掉；而having是分组之后对结果进行过滤
• where里不能有聚合函数，having可以带聚合函数

select StudentName, count(*) as 不及格科数
from Student join SelectCourse on Student.StudentID = SelectCourse.StudentID
where Score < 60
group by Student.StudentID
having count(*) >= 2