多表查询,也称为关联查询,指两个或更多个表一起完成查询操作
比如:员工表和部门表,这两个表依靠“部门编号”进行关联
#案例:查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
说明:上述多表查询中出现的问题称为:笛卡尔积的错误,结果是将每个员工分配了所有的部门所产生的
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数
笛卡尔积的错误会在下面条件下产生:
为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件
正确写法:
#案例:查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
注:在表中有相同列时,在列名之前加上表名前缀
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
注:多个表中有相同列时,必须在列名之前加上表名前缀
SELECT e.last_name,d.department_name,j.job_id
FROM employees e,departments d,jobs j#给表取别名
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_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;
一般来说我们都是用的非自连接,自连接连接的值都是在同一张表中
说明:当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义,然后两个表再进行内连接,外连接等查询
SELECT worker.last_name worker_name,manager.last_name manager
FROM employees worker,employees manager
WHERE worker.`manager_id`=manager.`employee_id`;
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
使用JOIN…ON子句创建连接的语法结构:
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
注:该嵌套逻辑类似我们使用的 FOR 循环,SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰可见
语法说明:
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
示例:
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`;
语法:
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT/RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现
需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
注意:执行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 在 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`;
在 SQL99 中你可以写成:
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
USING (department_id);
USING 指定了具体的相同的字段名称,需要在 USING的括号 () 中填入要指定的同名字段
同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接
它与下面的 SQL 查询结果是相同的:
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
表连接的约束条件可以有三种方式:WHERE, ON, USING
它与下面的 SQL 查询结果是相同的:
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
表连接的约束条件可以有三种方式:WHERE, ON, USING