目录
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个 关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进 行关联。
目前,如果要多表查询,就只能一张一张表去找,没有形成关联,速度与效率较慢,代码如下所示:
SELECT *
FROM employees
WHERE last_name = 'Abel'; # 找到所在部门
SELECT *
FROM departments
WHERE department_id = 80; # 找所在位置
SELECT *
FROM locations
WHERE location_id = 2500; # 找居住城市
#错误的实现方式:每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments; #查询出2889条记录
错误的原因:
笛卡尔积也称为 交叉连接 ,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。
#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录
为了避免笛卡尔积, 应该在 WHERE 加入有效的连接条件,具体代码如下所示。
SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.`department_id`;
注:在表中有相同列时,需要在列名之前加上表名前缀,如下所示。
#如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
#建议:从sql优化的角度,建议多表查询时,每个字段前都指明其所在的表。
如果有n个表实现多表的查询,则需要至少n-1个连接条件,具体代码如下所示:
#练习:查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
因为有些表名比较的长,所以可以考虑起别名的方法,但是要注意,根据代码的执行次序,表起了别名之后,只能使用别名来操作,具体代码如下所示:
#给表起别名,在SELECT和WHERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
#如果给表起了别名,一旦在SELECT或WHERE中使用表名的话,则必须使用表的别名,而不能再使用表的原名。
#如下的操作是错误的:
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;
等值连接 和 非等值连接
我们之前用等号连接的,都是等值连接,接下来主要介绍非等值连接,也就是在一个范围内相等的条件,代码如下所示:
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`;
运行结果如下所示:
我们之前都是不同的表相连接,现在主要介绍自己与自己相连接的情况,主要解决办法就是把一个表看成两个表,然后起不同的别名,再连接,具体代码如下所示:
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`;
运行结果如下所示:
因为SQL92语法,与SQL99语法对于实现内连接 和 外连接有所不同,于是分别讨论两种语法的实现,如下所示。
SQL92语法实现内连接:和我们之前的实现是一样的,就不再重复。
SQL92语法实现外连接:使用 + ----------MySQL不支持SQL92语法中外连接的写法
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);
# 用哪一部分缺少数据,就在那一部分加上一个加号,上面实现的是左外连接
SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。
SQL99语法实现内连接:
SELECT last_name,department_name,city
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;
SQL99语法实现外连接:
左外连接:
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
运行结果如下所示:
右外连接:
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
运行结果如下所示:
满外连接:mysql不支持FULL OUTER JOIN,具体解决方案,在后面给出。
合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并 时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION 操作符返回两个查询的结果集的并集,去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
注:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效 率。
中图:内连接
SELECT employee_id,department_name
FROM employees e 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`;
右上图:右外连接
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;
# 使用UNION ALL语句连接两个表
# 方式1:左上图 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
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 方式2:左中图 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
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;
# 右下图:左中图 UNION ALL 右中图
运行结果如下所示:
SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值 连接 。
在SQL92标准中:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
运行结果两者是相同的,还是建议前者,后者有局限性。
当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如:
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 JOIN departments d
USING (department_id);
这两种是相等的,但是有局限性,不能自连接,不太建议使用。