目录
实例:查询所有的员工的last_name,department_name信息
注意:所有 ---->且来自不通的表,外连接(有不匹配的数据)
SQL99 内连接语句 INNER JOIN ON / JOIN ON
SQL99 左外连接语句 LEFT OUTER JOIN / LEFT JOIN
表连接的约束条件可以有三种方式:WHERE, ON, USING
😹 作者: gh-xiaohe
😻 gh-xiaohe的博客
😽 觉得博主文章写的不错的话,希望大家三连(✌关注,✌点赞,✌评论),多多支持一下!!!
关于sql语句中子查询的位置总结_明快de玄米61的博客-CSDN博客_sql 子查询的位置
子查询
介绍
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL 4.1开始引入。
SQL 中子查询的使用大大增强了 SELECT 查询的能力,因为很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。
需求分析与问题解决
实际问题
现有解决方式:
方式一:
SELECT salary FROM employees WHERE last_name = 'Abel'; SELECT last_name,salary FROM employees WHERE salary > 11000 ;
方式二:自连接
SELECT e2.last_name,e2.salary FROM employees e1,employees e2 WHERE e1.last_name = 'Abel' #多表的连接条件 AND e2.`salary` > e1.`salary`;
方式三:子查询
SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );
子查询的基本使用
子查询的基本语法结构:
子查询(内查询)在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询,多行操作符对应多行子查询
子查询的分类
角度1:从内查询返回的结果的条目数
单行子查询 vs 多行子查询分类方式1:
按内查询的结果返回一条还是多条记录,将子查询分为 单行子查询 、 多行子查询 。
- 单行子查询
- 多行子查询
角度2:内查询是否被执行多次
相关子查询 vs 不相关子查询分类方式2:
按内查询是否被执行多次,分为 相关(或关联)子查询 和 不相关(或非关联)子查询
子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做不相关子查询。
如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询
比如:相关子查询的需求: 查询工资大于本部门平均工资的员工信息。(变化)
不相关子查询的需求:查询工资大于本公司平均工资的员工信息。(固定)
单行子查询
单行比较操作符
操作符 含义 = equal to > greater than >= greater than or equal to < less than <= less than or equal to <> not equal to
WHERE中的子查询
案例1:
查询工资大于149号员工工资的员工的信息
#题目:查询工资大于149号员工工资的员工的信息 SELECT employee_id,last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE employee_id = 149 );
结果
案例2
返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
#题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资 SELECT last_name,`job_id`,salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE `employee_id` = 141 ) AND salary > ( SELECT salary FROM employees WHERE `employee_id` = 143 );;
结果
案例3
返回公司工资最少的员工的last_name,job_id和salary
#题目:返回公司工资最少的员工的last_name,job_id和salary SELECT last_name,job_id,salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );
结果
案例4
查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id。
#题目:查询与141号员工的manager_id和department_id相同的其他员工 #的employee_id,manager_id,department_id。 #方式1: SELECT employee_id,manager_id,department_id FROM employees WHERE manager_id = ( SELECT manager_id FROM employees WHERE employee_id = 141 ) AND department_id = ( SELECT department_id FROM employees WHERE employee_id = 141 ) AND employee_id <> 141; ----------------------------------------- #方式2:了解 SELECT employee_id,manager_id,department_id FROM employees WHERE (manager_id,department_id) = ( SELECT manager_id,department_id FROM employees WHERE employee_id = 141 ) AND employee_id <> 141;
结果
HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
案例:
查询最低工资大于110号部门最低工资的部门id和其最低工资
#题目:查询最低工资大于110号部门最低工资的部门id和其最低工资 SELECT department_id,MIN(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 110 );
结果
CASE中的子查询
在CASE表达式中使用单列子查询
实例:
显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
#题目:显式员工的employee_id,last_name和location。 #其中,若员工department_id与location_id为1800的department_id相同, #则location为’Canada’,其余则为’USA’。 SELECT employee_id,last_name,CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id =1800 ) THEN 'Canada' ELSE 'USA' END "location" FROM employees;
结果
子查询中的空值问题
子查询中的空值问题 SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); # 没有此人,无job_id,无法查询出结果,返回空值
子查询不返回任何行
非法使用子查询
语句
#非法使用子查询 #错误:Subquery returns more than 1 row 多行子查询,应该应用多行子查询的操作符 SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
多行子查询
多行子查询使用单行比较符
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
多行子查询的操作符: IN ANY ALL SOME(同ANY)
操作符 含义 IN 等于列表中的任意一个 ANY 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 ALL 需要和单行比较操作符一起使用,和子查询返回的所有值比较 SOME 实际上是ANY的别名,作用相同,一般常使用ANY
In
案例1:返回每组员工中工资最少的的 员工id 和 姓名
SELECT employee_id, last_name FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id);
结果
ANY / ALL:
体会 ANY 和 ALL 的区别
非诚勿扰综艺,五个女生给你亮灯,
ANY 随便牵手哪一个都可以 只选一个
ALL 全部都带走ANY案例
返回其它 job_id中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号、姓名、job_id 以及salary
#题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、 #姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ); # 小于 9000 就可以
# 内查询 SELECT salary FROM employees WHERE job_id = 'IT_PROG'
结果
ALL案例1
返回其它 job_id中比 job_id 为 ‘IT_PROG’ 部门所有工资低的员工的员工号、姓名、job_id 以及salary
#题目:返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、 #姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE job_id <> 'IT_PROG' AND salary < ALL ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ); # 小于 4200 最小工资才可以
结果
ALL案例2
查询平均工资最低的部门id
让查询的结果充当一张表,此时列叫 avg_sal 而不是一个函数 MIN(avg_sal)
#题目:查询平均工资最低的部门id # 让查询的结果充当一张表,此时列叫 avg_sal 而不是一个函数 MIN(avg_sal) #MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套 #方式1: SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_sal) FROM( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) t_dept_avg_sal # Every derived table must have its own alias 每一个保留下来的表,起个别名 ); #方式2: SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( # <= ALL 小于等于所有的 相当于小于最小的 SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id )
结果
注意:MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套
空值问题
# 空值问题 # 结果中含有空值 内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况 SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT manager_id FROM employees # where manager_id IS NOT NULL );
注意:结果中含有空值 内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况
结果:返回的结果为空
子查询有两种分类方式:
方式一:单行子查询和多行子查询
方式二:相关子查询和非相关子查询
上面的方式(单行子查询和多行子查询)案例都是非相关子查询
非相关子查询
相关子查询
相关子查询执行流程
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询 。
相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。说明:子查询中使用主查询中的列
在 FROM 中使用子查询(相关子查询):
回顾案例:
查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
#回顾:查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id SELECT last_name,salary,department_id FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees )
结果
案例:
查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1:使用相关子查询 SELECT last_name,salary,department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE department_id = e1.department_id # 比较的是那个部门 );
#方式2:在FROM中声明子查询 SELECT e1.last_name,e1.salary,e1.department_id FROM employees e1,( SELECT department_id,AVG(salary) avg_sal # 需要传入department_id 用于做链接条件 FROM employees GROUP BY department_id) t_dept_avg_sal WHERE e1.department_id = t_dept_avg_sal.department_id AND e1.salary > t_dept_avg_sal.avg_sal; # avg_sal 此时是字段 不是函数 必须有别名
from型的子查询:子查询是作为from的一部分,子查询要用()引起来,并且要给这个子查询取别名, 把它当成一张“临时的虚拟的表”来使用。
结果
在ORDER BY 中使用子查询(相关子查询):
案例1
查询员工的id,salary,按照department_name 排序
#题目:查询员工的id,salary,按照department_name 排序 SELECT employee_id,salary FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE e.`department_id` = d.`department_id` ) ASC;
结果
案例2
若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id
SELECT * FROM job_history; SELECT e.employee_id,last_name,e.job_id FROM employees e WHERE 2 <= ( SELECT COUNT(*) FROM `job_history` j WHERE j.employee_id = e.employee_id );
结果
EXISTS 与 NOT EXISTS关键字
- 关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行:
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行:
- 不在子查询中继续查找
- 条件返回 TRUE
- NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
EXISTS 案例:
查询公司管理者的employee_id,last_name,job_id,department_id信息
#题目:查询公司管理者的employee_id,last_name,job_id,department_id信息 #方式1:自连接 SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id; #方式2:子查询 SELECT employee_id,last_name,job_id,department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ); #方式3:使用EXISTS SELECT employee_id,last_name,job_id,department_id FROM employees e1 WHERE EXISTS ( SELECT * # 此时 与 * 还是什么字段 已经不重要了,找到记录就要,没有找到记录就不要了 FROM employees e2 WHERE e1.`employee_id` = e2.`manager_id` # 相当于一条条数据往下找,找到满足的数据 为 true 就不像下寻找 );
NOT EXISTS案例:
查询departments表中,不存在于employees表中的部门的department_id和department_name
#方式1: 外连接 相当于 只需要个 一个表 去除 两个表的交集部门 SELECT d.department_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL; #方式2: SELECT department_id,department_name FROM departments d WHERE NOT EXISTS ( SELECT * FROM employees e WHERE d.`department_id` = e.`department_id` );
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据。
# 1) ALTER TABLE employees ADD(department_name VARCHAR2(14)); # 2) UPDATE employees e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据。
DELETE FROM employees e WHERE employee_id in (SELECT employee_id FROM emp_history WHERE employee_id = e.employee_id);
子查询可以应用的位置
SELECT ....,....,....(存在聚合函数) FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 (LEFT / RIGHT)JOIN ... ON .... WHERE 不包含聚合函数的过滤条件 GROUP BY ...,.... HAVING 包含聚合函数的过滤条件 ORDER BY ....,...(ASC / DESC ) LIMIT ...,....
结论:除了GROUP BY 和 LIMIT 之外,其他位置都可以声明子查询!
抛一个思考题?
问题:谁的工资比Abel的高?
解答:
#方式1:自连接 SELECT e2.last_name,e2.salary FROM employees e1,employees e2 WHERE e2.`salary` > e1.`salary` #多表的连接条件 AND e1.last_name = 'Abel'; #方式2:子查询 SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );
问题:自连接和子查询有好坏之分吗?
解答:自连接方式好
题目中可以使用子查询,也可以使用自连接。一般情况建议你使用自连接,因为在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。
可以这样理解:子查询实际上是通过未知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因此在大部分 DBMS 中都对自连接处理进行了优化。
相关子查询和非相关子查询的经典题型
非相关子查询
#8.查询平均工资最低的部门信息
#方式1:
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dept_avg_sal
)
);
#方式2:
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
);
#方式3: LIMIT
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) =(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1
)
);
#方式4:
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id
#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
#方式1:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) = (
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dept_avg_sal
)
);
#方式2:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) <= ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
);
#方式3: LIMIT
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary ) =(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 1
)
);
#方式4:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC
LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id
相关子查询
#19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询)
SELECT department_name
FROM departments d
WHERE 5 < (
SELECT COUNT(*)
FROM employees e
WHERE d.department_id = e.`department_id`
);
多表查询
案例分析与问题解决
- 笛卡尔积的错误会在下面条件下产生:
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
- 为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
- 加入连接条件后,查询语法:
-
#案例:查询员工的姓名及其部门名称 SELECT employee_id,department_name FROM employees,departments #两个表的连接条件 WHERE employees.`department_id` = departments.department_id;
- 在 WHERE子句中写入连接条件。
- 在表中有相同列时,必须在列名之前加上表名前缀。
多表查询的好处,相对于把所有字段整合到一张表中
- 减少了冗余的字段
- 查询时,数据量过大 io次数也增加 减少了io次数
- 方便维护
- 不涉及表锁定问题
多表查询分类讲解
分类1:等值连接 vs 非等值连接
等值连接
# 查询员工名 Abel 的在那个城市工作 #1、先查出 员工白中叫Abel 的人 的部门id SELECT * FROM employees WHERE last_name = 'Abel'; #2、根据部门 id 查询出 他所在的地点id SELECT * FROM departments WHERE `department_id` = 80; #3、根据他所在的地点id 查询出所在的城市 SELECT city FROM locations WHERE `location_id` = 2500; # 查询员工名 Abel 的在那个城市工作 SELECT last_name,locations.`city` FROM employees,departments,`locations` WHERE employees.department_id = departments.department_id AND last_name = 'Abel' AND departments.location_id = locations.`location_id`;
拓展1:多个连接条件与 AND 操作符
拓展2:区分重复的列名
- 多个表中有相同列时,必须在列名之前加上表名前缀。
- 在不同表中具有相同列名的列可以用 表名 加以区分。
拓展3:表的别名
- 使用别名可以简化查询。
- 列名前使用表名前缀可以提高查询效率。
注意:
如果给表起了别名,一旦在SELECT或WHERE中使用表名的话,则必须使用表的别名,而不能再使用表的原名。
拓展4:连接多个表
总结:连接 n个表,至少需要n-1个连接条件。
非等值连接
# 查询员工的工资水平 SELECT em.last_name,em.`salary`,job.`grade_level` FROM `employees` em,`job_grades` job WHERE em.`salary` BETWEEN job.`lowest_sal` AND job.`highest_sal`;
分类2:自连接 vs 非自连接
当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询。
#练习:查询员工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`;
分类3:内连接 vs 外连接
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
- 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
- 如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。
- 如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。
实例:查询所有的员工的last_name,department_name信息
注意:所有 ---->且来自不通的表,外连接(有不匹配的数据)
不正确
#练习:查询所有的员工的last_name,department_name信息 SELECT employee_id,department_name FROM employees e,departments d WHERE e.`department_id` = d.department_id; # 查出数据为106条数据 不正确 少了一个 为null的 数据
SQL92:使用(+)创建连接
- 在 SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。
- Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。
- 而且在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接。
语句
SELECT employee_id,department_name FROM employees e,departments d WHERE e.`department_id` = d.department_id(+);
SQL99语法实现多表查询
基本语法:使用JOIN...ON子句创建连接的语法
SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰可见。如果你采用 SQL92,可读性就会大打折扣。
语法说明:
- 可以使用 ON 子句指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON 子句使语句具有更高的易读性。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接
分类:
- 内连接 INNER JOIN ON
- 外连接 OUTER JOIN
- 左外连接 LEFT OUTER JOIN
- 右外连接 RIGHT OUTER JOIN
- 满外连接 FULL OUTER JOIN(MySQL 不支持,但是我们有办法实现)
SQL99 内连接语句 INNER JOIN ON / JOIN ON
# 两个表 SELECT last_name,department_name FROM `employees` e,`departments` d WHERE e.`department_id` = d.`department_id`; # 三个表 #SQL SELECT last_name,department_name,city FROM `employees` e JOIN `departments` d ON e.`department_id` = d.`department_id` JOIN locations l ON d.`location_id` = l.`location_id`;
外连接(OUTER JOIN)的实现
SQL99 左外连接语句 LEFT OUTER JOIN / LEFT JOIN
语法:
#实现查询结果是A SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句
语句
SELECT last_name,department_name FROM `employees` e LEFT OUTER JOIN `departments` d ON e.`department_id` = d.`department_id`; # 或者 可以省略 OUTER 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 LEFT OUTER JOIN `departments` d ON (e.`department_id` = d.`department_id`);
结果
SQL99 右外连接语句 RIGHT OUTER JOIN
语法:
#实现查询结果是B SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;
语句
SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
结果
满外连接(FULL OUTER JOIN)
- 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
- SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
- 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。
语句
合并查询 UNION的使用
合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。
语法格式:
SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2
UNION操作符
UNION 操作符返回两个查询的结果集的并集,去除重复记录。
UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
注意:
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率。
实例1 :查询部门编号>90或邮箱包含a的员工信息
# 方式一 SELECT * FROM employees WHERE email LIKE '%a%' OR `department_id` >= 90; # 方式二 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE`department_id` >= 90;
结果
7种SQL JOINS的实现
# 中图:内连接 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; # 左下图:满外连接 # 方式1:左上图 UNION ALL 右中图 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; # 方式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`; # 右下图:左中图 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;
SQL99语法新特性
自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 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`; # NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`,然后进行`等值连接`。 SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
结果
USING连接
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);
结果
章节小结
表连接的约束条件可以有三种方式:WHERE, ON, USING
- WHERE:适用于所有关联查询
- ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写,但分开写可读性更好。
- USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字段值相等
注意关联条件:
- 把关联条件写在where后面
- 把关联条件写在on后面,只能和JOIN一起使用
- 把关联字段写在using()中,只能和JOIN一起使用,而且两个表中的关联字段必须名称相同,而且只能表示=
- n张表关联,需要n-1个关联条件
注意:
我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下降得很严重,因此不要连接不必要的表。在许多 DBMS 中,也都会有最大连接表的限制。
【强制】超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时, 保证被关联的字段需要有索引。
说明:即使双表 join 也要注意表索引、SQL 性能