关联查询 --- 2020-09-16

1.内连接

select 
e.empName,d.deptName
from t_employee e
INNER JOIN t_dept d
ON e.dept = d.id;

2.左连接

SELECT e.empName,d.deptName
from t_employee  e
LEFT  OUTER  JOIN   t_dept   d 
on d.id = e.dept;

3.右连接
SELECT e.empName,d.deptName
from t_employee e
RIGHT OUTER JOIN t_dept d
on d.id = e.dept;

4.全外连接

select e.empName,d.deptName
FROM t_employee e
left JOIN t_dept d
ON e.dept = d.id
UNION
select e.empName,d.deptName
FROM t_employee e
RIGHT JOIN t_dept d
ON e.dept = d.id;

5.自连接
自连接查询
自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名
例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表
SELECT e.empName,b.empName
from t_employee e
LEFT JOIN t_employee b
ON e.bossId = b.id;