MySQL子查询

igxiaoshan Lv5
  2023-10-30 14:15:41 2023-10-30 14:15:41 创建   2023-10-30 14:18:41 2023-10-30 14:18:41 更新      1.5k 字  5 分钟  

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者

需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集

合）进行比较。

基本使用

  • 语法结构
    • 子查询(内查询)在主查询之前一次执行完成
    • 子查询的结果被主查询(外查询)使用
  • 注意事项
    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询,多行操作符对应多行子查询

子查询分类

  • 分类方式一
    • 根据内查询的结果返回一条还是多条记录,将子查询分为单行子查询和多行子查询
      • 单行子查询
      • 多行子查询
  • 分类方式二
    • 根据内查询是否被执行多次,将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询
      • 子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做不相关子查询
      • 同样,如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询.

单行子查询

单行操作符

操作符 含义
= equal to
> greater than
>= greater then or equal to
< less then
<= less then or equal to
<> not equal to
  • 子查询中空值问题
    • 如果内查询中查询的结果还是空值,子查询不返回任何行
  • 非法使用子查询
    • 多行子查询使用单行比较符
      • 报错: 错误代码1242;Subquery returns more than 1 row

多行子查询

  • 也称为集合比较子查询
  • 内查询返回多行
  • 使用多行比较操作符

多行比较操作符

操作符 含义
IN 等于列表中的任意一个
ANY 需要和单行比较操作符一起使用,和子查询返回的某一个值比较
ALL 需要和单行比较操作符一起使用,和子查询返回的所有值比较
SOME 实际上是ANY的别名,作用相同,一般常用ANY

体会 ANY 和 ALL 的区别

  • ANY查询结果,比较>就是ANY结果的最小值,<就是ANY结果的最大值
  • ALL查询结果,比较>就是ALL结果的最大值,<就是ALL结果的最小值

空值问题

  • 内查询中的结果不存在主查询中,会返回空值
    • no rows selected

相关子查询

相关子查询执行流程

如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为关联子查询

相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询

GET 从主查询中获取候选列 ====> EXECUTE 子查询使用主查询的数据 ====> USE 如果满足子查询的条件则返回该行

1
2
3
4
5
6
7
8
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator
					( SELECT column1, column2
                     FROM table2
                     WHERE expr1 = outer.expr2);
                     
-- 注意: 子查询使用主查询中的列
  • 在FROM中使用子查询

FROM 型的子查询: 子查询是作为FROM的一部分, 子查询要用 ()引起来, 并且要给这个子查询取别名, 把它当做一张”临时的虚拟的表”来使用

1
2
3
4
5
6
7
-- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, emp1.department_id
FROM employees emp1,(SELECT department_id,
AVG(salary) emp_avg_sal FROM employees GROUP BY department_id) emp2
WHERE 
emp1.salary > emp2.emp_avg_sal
AND emp1.department_id = emp2.department_id
  • 在 GROUP BY 中使用子查询
1
2
3
4
5
6
7
8
-- 查询员工的id,salary,按照department_name 排序
SELECT employee_id, salary
FROM employees e1
GROUP BY (
			SELECT department_name
			FROM departments d
			WHERE e1.department_id = d.department_id
)

EXISTS和NOT EXISTS关键字

  • 关联子查询通常也会和EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行
  • 如果在子查询中不存在满足条件的行
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行
    • 不在子查询中继续查找
    • 条件返回TRUE
  • NOT EXISTS 关键字表示吐过不存在某种条件,则返回TRUE,否则返回FALSE
1
2
3
4
-- 查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees emp1
WHERE EXISTS (SELECT * FROM employees emp2 WHERE emp1.employee_id = emp2.manager_id);
1
2
3
4
-- 查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d1
WHERE NOT EXISTS (SELECT * FROM employees WHERE d1.department_id = department_id);

相关拓展

1
2
3
4
5
6
7
8
9
10
11
12
13
--谁的工资比Abel的高？
# 自连接
SELECT e2.last_name, e2.salary
FROM employees e1 , employees e2
WHERE e1.last_name = 'Abel'
AND e1.salary <= e2.salary
ORDER BY salary;

# 子查询
SELECT last_name, salary
FROM employees
WHERE salary >= (SELECT salary FROM employees WHERE last_name = 'Abel')
ORDER BY salary;
  • 哪种查询方式好
    • 以上的查询可以使用子查询,也可以使用自连接.一般情况建议使用自连接,因为在许多DBMS的处理过程中,对于自连接的处理速度要比子查询快得多
    • 可以这样理解: 子查询实际上是通过为知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因为在大部分DBMS中都对自连接处理进行了优化.
此页目录
MySQL子查询
  1. 基本使用
  2. 子查询分类
    1. 单行子查询
    2. 多行子查询
    3. 相关子查询