SQL多表查询：左外连接、右外连接、满外连接、UNION ALL

1 多表查询分类

1.1 等值连接 VS 非等值连接

1.1.1 非等值连接

不是等号连接

-- 非等值连接
-- 非等值连接
SELECT
	last_name,
	salary,
	grade_level 
FROM
	employees e,
	job_grades j 
WHERE
	e.salary >= j.grade_level 
	AND e.salary <= j.highest_sal -- 或者
-- e.salary BETWEEN j.grade_level
-- AND j.highest_sal

1.1.2 等值连接

等号连接

1.2 自连接 VS 非自连接

1.2.1 自连接

自己连接自己

-- 查询员工id，员工姓名，管理者的id和姓名
SELECT
	t1.employee_id,
	t1.first_name,
	t1.manager_id,
	t2.first_name 
FROM
	employees t1,
	employees t2 
WHERE
	t1.manager_id = t2.employee_id

1.2.2 非自连接

不自己连接自己

1.3 内连接 VS 外连接

1.3.1 内连接

只有满足条件的才会被查出来

SQL92语法

SELECT
	employee_id,
	last_name,
	department_name
FROM
	employees e,
	departments d 
WHERE
	e.department_id = d.department_id

SQL99语法

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id;

1.3.2 外连接

除了满足条件的会被查出来之外，还会查询出左表或右表不匹配的行。

1.3.2.1 左外连接

左表不匹配部分+交集

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	LEFT JOIN departments d ON e.department_id = d.department_id;

1.3.2.2 右外连接

右表不匹配部分+交集

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id;

1.3.2.2 满外连接

左表右表不匹配的部分+交集

Mysql不支持FULL OUT JOIN

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	FULL OUT JOIN departments d ON e.department_id = d.department_id 

要用到下面关键字，实例在最后。

• UNION（合并）：左外+右外+去重 = 满外连接（效率慢）
• UNION ALL（合并所有）：满外连接的基础上不去重（左表不匹配部分+交集+右表不匹配部分=满外连接）
  

2 所有JOIN演示图

3 求单表不匹配数据

3.1 求左表不匹配的部分实现

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
	d.department_id IS NULL;

3.2 求右表不匹配的部分实现

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
	e.department_id IS NULL;

5 利用UNION ALL实现满外连接

UNION ALL要比UNION快，能用UONION ALL就用UNION ALL。


这两个图拼接就可以得到一个满连接

SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
	e.department_id IS NULL UNION ALL
SELECT
	employee_id,
	first_name,
	department_name 
FROM
	employees e
	LEFT JOIN departments d ON e.department_id = d.department_id