MySQL自连接和内连接和外连接_左外连接+右外连接

自连接：将一张表看作两张表

练习：查询员工id，员工姓名及其管理者的id和姓名

select emp.employee_id,
	   emp.last_name,
	   mgr.employee_id,
	   mgr.last_name
from employees emp,employees mgr
where emp.manager_id = mgr.employee_id;
1
2
3
4
5
6
7
8

内连接

只是把左表和右表满足连接条件的数据查出来，其它的数据都没有要！！！

select employee_id,department_name
from employees e join departments d
on e.`department_id`=d.`department_id`
1
2
3

外连接

JOIN … ON

左外连接 left join…on

左外连接，左表和右表满足条件的数据，和左表中不满足条件的数据！！！

练习：查询所有员工的last_name,department_name信息

select last_name,department_name 
from employees e left join departments d
on e.`department_id`=d.`department_id`;
1
2
3
4
5

右外连接 right join … on

右外连接，右表和左表满足条件的数据，和右表中不满足条件的数据！！！

练习：查询所有员工的last_name,department_name信息

select last_name,department_name 
from departments d right join employees e
on e.`department_id`=d.`department_id`;
1
2
3
4
5

七种 SQL JOINS 的实现

UNION的使用

合并查询结果

• 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。
• 合并时，两个表对应的列数和数据类型必须相同，并且相互对应。
• 各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。
如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

1、内连接（两表只要满足条件的）

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3

2、左外连接（左和右满足条件的，和左中不满足条件的）

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3

3、右外连接（右和左满足条件的，和右中不满足条件的）

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3

4、在左外连接的基础上，右表取null值（满足条件的肯定不是null，我们不取）

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
1
2
3
4

5、在右外连接的基础上，我们取左表的null值

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
1
2
3
4

6、右外连接取左表null值，和左外连接合并UNION ALL

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
1
2
3
4
5
6
7
8

7、

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
1
2
3
4
5
6
7
8
9