SQL——多表查询

显示所有员工的姓名，部门号和部门名称

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
on e.department_id = d.department_id;

查询90号部门员工的job_id和90号部门的location_id

SELECT job_id, location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;

选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name, department_name, d.location_id, city
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;

选择city在Toronto工作的员工的 last_name , job_id , department_id, department_name

SELECT last_name, job_id, d.department_id, department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE city = 'Toronto';

查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

SELECT department_name, street_address, last_name, job_title, salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
JOIN jobs j
ON e.job_id = j.job_id
WHERE department_name = 'Executive';

选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

SELECT e1.last_name employees, e1.employee_id 'Emp#', e2.last_name manager, e2.employee_id 'Mgr#'
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

查询哪些部门没有员工

SELECT d.department_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

查询哪个城市没有部门

SELECT l.location_id, l.city
FROM locations l
LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id IS NULL;

查询部门名为 Sales 或 IT 的员工信息

SELECT e.employee_id, e.last_name, department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE department_name = 'Sales' OR department_name = 'IT';

所有有门派的人员信息（ A、B两表共有）

SELECT e.*
FROM t_emp e
JOIN t_dept d
ON e.deptId = d.id;

列出所有用户，并显示其机构信息（A的全集）

SELECT e.*, d.deptName, d.address
FROM t_emp e
left JOIN t_dept d
ON e.deptId = d.id;

标题列出所有门派（B的全集）

SELECT *
FROM t_dept d;

所有不入门派的人员（A的独有）

SELECT e.*
FROM t_emp e
LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE d.id IS NULL;

所有没人入的门派（B的独有）

SELECT *
FROM t_emp e
RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;

列出所有人员和机构的对照关系(AB全有)

SELECT *
FROM t_emp e
LEFT JOIN t_dept d
ON e.deptId = d.id
UNION
SELECT *
FROM t_emp e
RIGHT JOIN t_dept d
ON e.deptId = d.id;

列出所有没入派的人员和没人入的门派

（A的独有+B的独有）

SELECT *
FROM t_emp e
LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE d.id IS NULL
UNION
SELECT *
FROM t_emp e
RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;