7种SQL JOINS.png
一、内连接(中图 A∩B )
- 特点:显示满足
emp.department_id = dep.department_id
的数据
SELECT *
FROM employees emp
JOIN departments dep
ON emp.department_id = dep.department_id
内连接结果.png
二、左外连接(左上图)
SELECT emp.employee_id, dep.department_id
FROM employees emp
LEFT JOIN departments dep
ON emp.department_id = dep.department_id
左外连接结果.png
三、右外连接(右上图)
SELECT emp.employee_id, dep.department_id
FROM employees emp
RIGHT JOIN departments dep
ON emp.department_id = dep.department_id
右外连接结果.png
四、左中图( A - A∩B )
SELECT emp.employee_id, dep.department_id
FROM employees emp
LEFT JOIN departments dep
ON emp.department_id = dep.department_id
WHERE dep.department_id IS NULL
A - A∩B 结果.png
五、右中图( B - A∩B )
SELECT emp.employee_id, dep.department_id
FROM employees emp
RIGHT JOIN departments dep
ON emp.department_id = dep.department_id
WHERE emp.department_id IS NULL
B - A∩B 结果.png
六、满外连接
- 特点:显示 A 和 B 表中的数据,但是要去重
- 左中图 + 右上图 A∪B
SELECT emp.employee_id, dep.department_id
FROM employees emp
LEFT JOIN departments dep
ON emp.department_id = dep.department_id
WHERE dep.department_id IS NULL
UNION ALL
SELECT emp.employee_id, dep.department_id
FROM employees emp
RIGHT JOIN departments dep
ON emp.department_id = dep.department_id
七、右下图
- 特点:显示 A 和 B 表中的数据,但是要去除重叠部分数据
- 左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
SELECT emp.employee_id, dep.department_id
FROM employees emp
LEFT JOIN departments dep
ON emp.department_id = dep.department_id
WHERE dep.department_id IS NULL
UNION ALL
SELECT emp.employee_id, dep.department_id
FROM employees emp
RIGHT JOIN departments dep
ON emp.department_id = dep.department_id
WHERE emp.department_id IS NULL
image.png
小结
- UNION 操作符返回连个查询的结果集的并集,并且过滤了重复数据
- UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重
- 注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效
率
网友评论