7.7 右下图
可以通过左中图与右中图进行并集操作实现。
# 右下图 # 左中图 SELECT e.employee_id, d.department_id FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL UNION ALL # 右中图 SELECT e.employee_id, d.department_id FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IS NULL ;
8 自然连接
自然连接会自动查询两张连接表中所有字段名相同的字段，然后根据所有字段名相同的字段进行等值连接。可以把自然连接理解为多个字段的等值连接。
SQL99 在 SQL92 的基础上提供了 NATURAL JOIN 用来表示自然连接。
在 employees 表中和 departments 表中具有两个字段名相同的字段：
基于 employees 表和 departments 表进行自然连接查询：
# 自然连接查询 SELECT e.employee_id, e.department_id, d.department_id, e.manager_id, d.manager_id FROM employees e NATURAL JOIN departments d;
等价于：
SELECT e.employee_id, e.department_id, d.department_id, e.manager_id, d.manager_id FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id;
9 USING 连接
进行连接的时候，SQL99 还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。
使用 USING 指定数据表里的同名字段后，会自动在两个表中查找指定的字段，根据指定的字段进行等值连接。
SELECT e.employee_id, d.department_id FROM employees e JOIN departments d USING (department_id);
等价于：
SELECT e.employee_id, d.department_id FROM employees e JOIN departments d ON e.department_id = d.department_id;
10 补充
在使用 JOIN 进行连接查询时，可以将连接条件一起写在连接的后面，如下：
SELECT last_name,job_title,department_name FROM employees INNER JOIN departments INNER JOIN jobs ON employees.department_id = departments.department_id AND employees.job_id = jobs.job_id;
建议：
一个连接后面跟着对应的连接条件，即一个 JOIN 后面跟着对应的 ON。
SELECT last_name,job_title,department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id INNER JOIN jobs ON employees.job_id = jobs.job_id;
11 多表查询练习
【题目】
# 1.显示所有员工的姓名，部门号和部门名称。 # 2.查询90号部门员工的job_id和90号部门的location_id # 3.选择所有有奖金的员工的 last_name , department_name , location_id , city # 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name # 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ # 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 # employees Emp# manager Mgr# # kochhar 101 king 100 # 7.查询哪些部门没有员工 # 8. 查询哪个城市没有部门 # 9. 查询部门名为 Sales 或 IT 的员工信息
【解答】
1.显示所有员工的姓名，部门号和部门名称。
# 1.显示所有员工的姓名，部门号和部门名称。 SELECT e.last_name, d.department_id, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
# 2.查询90号部门员工的job_id和90号部门的location_id SELECT e.job_id, d.location_id FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_id = 90;
或
SELECT e.job_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id = 90;
3.选择所有有奖金的员工的 last_name , department_name , location_id
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city SELECT e.last_name, d.department_name, l.location_id, l.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 ;
4.选择city在Toronto工作的员工的 last_name , job_id , department_id
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name SELECT e.last_name, e.job_id, d.department_id, d.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';
或
SELECT e.last_name, e.job_id, d.department_id, d.department_name FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.city = 'Toronto';
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ SELECT d.department_name, l.city, e.last_name, e.job_id, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id WHERE department_name = 'Executive';
或
SELECT d.department_name, l.city, e.last_name, e.job_id, e.salary FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND department_name = 'Executive';
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
| employees | Emp# | manager | Mgr# |
| kochhar | 101 | king | 100 |
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 # employees Emp# manager Mgr# # kochhar 101 king 100 SELECT e.last_name "employees", e.employee_id "Emp#", m.last_name "manager", m.employee_id "Mgr#" FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id;
7.查询哪些部门没有员工
# 7.查询哪些部门没有员工 SELECT d.department_id FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL;
- 查询哪个城市没有部门
# 8. 查询哪个城市没有部门 SELECT l.city, l.location_id FROM locations l LEFT JOIN departments d ON l.location_id = d.location_id WHERE d.department_id IS NULL ;
- 查询部门名为 Sales 或 IT 的员工信息
# 9. 查询部门名为 Sales 或 IT 的员工信息 SELECT e.employee_id, e.last_name, d.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name IN ('Sales', 'IT');
或
SELECT e.employee_id, e.last_name, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_name IN ('Sales', 'IT');
