MySQL高级性能优化---JOIN连接

1. 先准备测试表数据

创建两种表部门表(department)和员工表(employee)

2. 内连接

2.1 作用

查询两张表的共有部分

2.2 语句

Select <select_list> from tableA A Inner join tableB B on A.key = B.key

select * from employee e INNER JOIN department d on e.dep_id = d.id 

3. 左连接与右连接

以左连接举例

3.1 作用

把左边表的内容全部查出，右边表只查出满足条件的记录

3.2 语句

Select <select_list> from tableA A left join tableB B on A.key = B.key

select * from employee e LEFT JOIN department d on e.dep_id = d.id 

4. 查询左表独有数据和查询右表独有数据

以左连接举例

4.1 作用

4.2 语句

Select <select_list> from tableA A left join tableB B on A.key = B.key WHERE B.key is NULL

select * from employee e LEFT JOIN department d on e.dep_id = d.id where d.id is NULL

5. 全连接

5.1 作用

查询两张表的所有信息

5.2 语句

Select <select_list> from tableA A Full Outter join tableB B on A.key = B.key
mysql不支持full outter join， 下边的mysql全连接的另一种写法

select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION
select * from employee e RIGHT JOIN department d on e.dep_id = d.id

6. 查询左右表各自独有的数据

5.1 作用

查询两张表独有的数据

5.2 语句

select * from employee e LEFT JOIN department d on e.dep_id = d.id where d.id is null
UNION
select * from employee e RIGHT JOIN department d on e.dep_id = d.id where e.id is null