[打卡]张士晨——多表查询

SELECT employee_id,department_name
from employees e right JOIN departments d
on e.department_id=d.department_id;
#sql99

SELECT employee_id,department_name
from employees e join departments d
on e.department_id=d.department_id;
#sql92

SELECT employee_id,department_name
from employees e , departments d
WHERE e.department_id=d.department_id;
#左外连接

SELECT employee_id,department_name
FROM employees e left join departments d
on e.department_id=d.department_id;
#右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
on e.department_id=d.department_id;

#左中
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;
#右中
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;
#左下
#满接
#左上+右中
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;
#右下+左中
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 d.department_id is null;
#右上+左中
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 department_name d
on e.department_id=d.department_id
WHERE d.department_id is null;
#左中+右中
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;
null
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;