MySQL的多表查询（5）：7种SQL JOINS的实现

第1种，内连接

# 第1种，内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id; -- 返回106行记录

第2种，左外连接

# 第2种，左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id; -- 返回107行记录

第3种，右外连接

# 第3种，右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id; -- 返回122行记录

第4种

# 第4种
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL; -- 返回1行记录

第5种

# 第5种
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 返回16行记录

第6种，满外连接

UNION和UNION ALL

使用UNION、UNION ALL，可以合并查询结果。
其中，UNION，会去重；UNION ALL，不会去重。

第2种（左外连接） UNION ALL 第5种，得到满外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 返回123行记录

第3种（右外连接） UNION ALL 第4种，得到满外连接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 返回123行记录

第7种

UNION和UNION ALL

使用UNION、UNION ALL，可以合并查询结果。
其中，UNION，会去重；UNION ALL，不会去重。

由第4种、第5种UNION ALL得到

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; -- 返回17行记录