04_DQL 的连接查询 - 内连接、外连接 + 案例

这篇博客介绍 SQL 中数据查询语言中的连接查询。连接查询又称为多表查询，当查询字段来自多个表示，我们就需要用到连接查询。

我们先来看看连接查询的分类。

连接查询分类

• 按年代分类，可以分为 sql92 标准（仅支持内连接）和 sql99 标准（支持所有的内连接 + 左外和右外 + 全外连接）。
• 按功能分类
  • 内连接（等值连接、非等值连接、自连接）
  • 外连接（左外连接、右外连接、全外连接）
  • 交叉连接

SQL99 语法

今天我们介绍 sql99 语法。

基本的语法：

SELECT 查询列表
FROM 表1 别名【连接类型】
JOIN 表2 别名
ON  连接条件
WHERE 筛选条件
GROUP BY..
HAVING ..
ORDER BY...

分类：

• 内连接：inner
• 外连接
  • 左外： left outer
  • 右外: right outer
  • 全外：full outer
• 交叉连接：cross

内连接

基本语法是：

SELECT 查询列表
FROM 表1 别名
INNER join 表2 别名
ON 连接条件

内连接分为等值连接、非等值连接还有自连接。

等值连接

案例1：查询员工名、部门名

select last_name, department_name
from employees e
inner join departments d
on e.deparment_id = d.department_id;

案例2：查询名字中包含 e 的员工名和工种名。

select last_name, job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where e.last_name like '%e%'

案例3：查询部门个数大于等于3的城市名和部门个数。

select city, count(*)
from locations l
inner join departments d
on l.location_id = d.location_id
group by city
having count(*) >= 3

案例4：查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序。

select count(*), department_name
from employees e
inner join departments d
on e.department_id = d.department_id
group by department_name
having count(*) > 3
order by count(*) desc

案例5：查询员工名、部门名、工种名，并按部门降序。（三表连接）

select last_name, department_name, job_title
from employees e
inner join departments d
on e.department_id = d.department_id
inner join jobs j
on e.job_id = j.job_id
order by department_name desc

特点：

• 添加排序、分组和筛选
• inner 可以省略
• 筛选条件放在where后面，连接连接放在on后面，便于阅读。
• 内连接等值连接和sql92语法中的效果是一样的。

非等值连接

案例1：查询员工的工资级别

select salary, grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;

案例2：查询每个工资级别 > 2的个数，并且按工资级别降序排序。

select count(*), grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*) > 2
order by grade_level desc

自连接

案例1：查询员工名和上级名

select e.last_name, m.last_name
from employees e
join employees m
on e.manager_id = m.employee_id

外连接

案例引入：查询没有男朋友的女神名
应用场景：用于查询一个表中有，另外一个表中没有。

select b.name
from beauty b
left join boys bo
on b.boyfriend_id = bo.id
where bo.id is null

特点：

• 外连接的查询结果为主表中所有记录
  • 如果从表中有和它匹配的，则显示匹配的值
  • 如果没有与之匹配的，则显示 NULL
  • 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
• 左外连接，left join 左边的是主表
• 右外连接，right join 右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果。

案例1：查询哪个部门没有员工？

select d.*, e.employee_id
from departments d
left outer join employees e
on d.department_id = e.department_id
where e.employee_id is null

全外连接

select b.*, bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.id

全外连接 = 内连接的结果 + 表1中表2中没有 + 表2中有但表1中没有

交叉连接

select b.*, bo.*
from beauty b
cross join boys bo;

交叉连接的结果是笛卡尔积。

案例

1. 查询编号大于 3 的女神的男朋友信息，如果有列出详细信息，没有null填充。

select bo.*, b.id, b.name
from beauty b
left outer join boys bo
on bo.id = b.boyfriend_id
where b.id > 3;

2. 查询哪个城市没有部门

select city, d.*
from locations l
left outer join departments d
on l.location_id = d.locaiton_id
where d.department_id is null;

3. 查询部门名为 SAL 或 IT 员工信息

select e.*
from employees e
right outer join departments d
on d.department_id = e.department_id
where d.department_name = 'IT'
or d.department_name = 'SAL';
或者
where d.department_name in ('SAL','IT')

这里采用右外连接是考虑到有可能部门没有员工。