多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。


笛卡尔积 （交叉连接，CROSS JOIN）
SELECT last_name,department_name FROM employees, departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments

出现笛卡尔积的原因是：多表查询时没有连接条件或连接条件无效

#案例：查询员工的姓名及其部门名称
SELECT employees.last_name, departments.department_name FROM employees, departments
WHERE employees.department_id = departments.department_id;
注意：如果某个员工没有部门（员工的部门id为NULL）,则不会查询这个员工出来；

表别名
SELECT emp.last_name, dep.department_name FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id;
注意：表起了别名，就不能再select和where中再用表名作为前缀了；

多表查询的分类
角度1（连接条件）：等值连接(上面的就是等值) vs 非等值连接
角度2（自我引用）：自连接 vs 非自连接
角度3：内连接 vs 外连接

非等值连接



SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
-- WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

非自连接
不同表之间的关联查询，上面写的都是非自连接

自连接
自己表连接自己表

查询 员工id、员工姓名、管理者id、管理者姓名
SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id

---

内连接 和 外连接
上面写的sql语句，都是内连接
内连接：将满足连接条件的数据查出来了，而没满足条件的其他数据都没有要，这种就叫内连接
比如：员工表和部门表，假如员工表的某个员工没有设置部门id（NULL），那么内连接查询出来是不包含这个员工的；
select last_name, department_name from employees e
inner join departments d on e.department_id = d.department_id;    -- inner可以省略


外连接
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

题目：查询所有的员工的last_name,department_name的信息
select last_name, department_name from employees e
left outer join departments d on e.department_id = d.department_id;    -- outer 可以省略

---

UNION [ALL]
两个表合并成一个，两个表的列数和数据类型必须一致且相互对应；
UNION：返回两个查询结果集的并集，去除重复记录；
UNION ALL：符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

---

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

#右中图：B-A∩B
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

SQL99 新特性 - 自然连接、USING连接

查看:4454 回复: 0

我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》

# 7.查询哪些部门没有员工

# 8. 查询哪个城市没有部门