子查询

转载自：https://zhuanlan.zhihu.com/p/603197412?utm_id=0

前言

概念：

出现在其他语句内部的SELECT语句，称为子查询或内查询（其他语句：不限于SELECT语句，不过最常出现在SELECT语句内部）。与之相对地，外部的查询语句，称为外查询或主查询。

分类：

• 按子查询出现的位置：SELECT后面、FROM后面、WHERE或HAVING后面、EXISTS后面（相关子查询）

• 按查询结果的行列数：标量子查询（结果只有一行一列）、列子查询（结果只有一列多行，也称为多行子查询）、行子查询（结果有一行多列或多行多列）、表子查询（结果一般为多行多列）

SELECT后面：仅仅支持标量子查询

FROM后面：支持表子查询

WHERE或HAVING后面：主要支持标量子查询和列子查询，行子查询用得比较少

EXISTS后面：支持表子查询

1. WHERE或HAVING后面的子查询

特点：

（1）子查询放在小括号内

（2）子查询一般放在条件的右侧

（3）标量子查询一般搭配着单行操作符使用，比如：>、<、>=、<=、=、!=；列子查询一般搭配着多行操作符使用，比如IN、ANY/SOME、ALL

（4）子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。

1.1 标量子查询

子查询结果返回标量，即1行1列。

• 案例1：谁的工资（salary）比 Abel 高（注：Abel是姓氏）？已知：表employees中有员工信息，包含工资字段salary。

分析：从employees中先获取姓氏为Abel的工资，然后再筛选比Abel的工资高的员工。

# ①查询Abel（姓氏）的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';

# ②查询员工的信息，满足salary>①的结果
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

因为子查询的结果只有一行一列（表中Abel姓氏的员工只有一位，对应的工资也只有1个），所以是标量子查询。

• 案例2：返回job_id与141号（employee_id）员工相同，salary比143号员工多的员工姓名（first_name和last_name），job_id和工资（salary)。注：以上字段均在employees表中。

分析：先从employees表中找到员工id为141号的工作id，还有员工id为143号的工资，然后再筛选工作id与前者相等，工资比后者高的员工。

# ①查询141号员工的工作id
SELECT job_id
FROM employees
WHERE employee_id = 141;

# ②查询143号员工的工资
SELECT salary
FROM employees
WHERE employee_id = 143;

# ③查询员工的姓名、job_id和工资，要求job_id=①且salary>②
SELECT first_name,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
);

这个案例中有2个子查询，说明WHERE后面可以支持多个子查询。

• 案例3：返回公司工资最少的员工last_name，job_id，salary。注：以上字段均在employees表中。

分析：从employees表中找到最少的工资，然后筛选出工资数目是最少工资的员工last_name，job_id，salary。

# ①查询公司的最低工资
SELECT MIN(salary)
FROM employees;

# ②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

• 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资。

分析：从employees表中找到50号部门的最低工资，然后筛选部门最低工资大于前者的部门id和其最低工资。

# ①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;

# ②查询部门id,最低工资，满足最低工资>①
SELECT department_id,MIN(salary) AS min_salary
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

这里的子查询是嵌套在HAVING后面的，因为涉及按部门id分组求最低工资。

注意：子查询的结果若不是一行一列，不能使用标量子查询，不能搭配单行操作符。

1.2 列子查询（多行子查询）

子查询结果返回1列多行。

事实上，IN()与= ANY()达到的效果一样；NOT IN()与!= ALL()效果一样。

• 案例1：返回location_id是1400或1700的部门中的所有员工姓名。location_id字段在departments表中，员工姓名在employees表中，二表共有字段是department_id。

分析：从departments表中查询location_id是1400或1700的部门id（department_id），然后从employees表中找到部门id在前者中的员工姓名。

# ①查询location_id是1400或1700的部门id（在departments表中）
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700);

# ②查询员工姓名，要求部门号是①列表中的一个（在employees表中）
SELECT first_name,last_name
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

子查询中返回location_id是1400或1700的部门id是多行的，因此是列子查询（多行子查询）。

• 案例2：返回其它工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号（employee_id）、last_name、job_id、salary。注：以上字段均在employees表中。

分析：从employees表中找到job_id为'IT_PROG'工种的所有工资，然后查询其它工种中工资低于前者列表中任一值的员工信息，注意：其它工种就表明job_id不能等于'IT_PROG'。

# ①查询job_id为'IT_PROG'的部门工资
SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG';

# ②查询employee_id、last_name、job_id、salary，要求salary<①中任一值，且job_id不是'IT_PROG'
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT salary 
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';

• 案例3：返回其它工种中比job_id为'IT_PROG'工种所有工资都低的员工的员工号（employee_id）、last_name、job_id、salary。注：以上字段均在employees表中。

# ①查询job_id为'IT_PROG'的部门工资
SELECT salary 
FROM employees
WHERE job_id = 'IT_PROG';

# ②查询employee_id、last_name、job_id、salary，要求salary<①中所有值，且job_id不是'IT_PROG'
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT salary 
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG';

1.3 行子查询

子查询结果返回1行多列或多行多列，重点是多列。

• 案例1：查询员工编号（employee_id）最小且工资（salary）最高的员工信息。

分析：这道题可以采用标量子查询的思想，具体做法类似1.1节中的案例2。从employees表中分别查询最小的员工编号和最高的员工工资，筛选同时符合这两个条件的员工信息。

# ①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

# ②查询最高的员工工资
SELECT MAX(salary)
FROM employees;

# ③查询员工信息，要求employee_id=①且salary=②
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
) AND salary = (
	SELECT MAX(salary)
	FROM employees
);

但是，我们这里可以换一种思路，采用行子查询，查询结果不变。

# 行子查询
SELECT *
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

行子查询具有局限性，子查询的多个字段（列）需要有一样的操作符，比如这个案例中employee_id和salary都用=这个操作符进行筛选，因此可以把（employee_id,salary）看作一个虚拟的字段。

2. SELECT后面的子查询

SELECT后面仅仅支持标量子查询，即1行1列。

• 案例1：查询每个部门（department_id）的员工个数。注：不能仅从employees表中计算员工个数，因为employees表中只包含有员工的部门，存在一些部门没有员工的情况。

分析：departments表中罗列了所有的部门，有些部门有员工，员工信息出现在employees表中，可在employees表中统计个数，但有些部门没有员工，员工个数就是0。因此，主查询的表是departments，子查询的表是employees，且子查询中必须要满足这两个表格的部门id相等。具体查询语句如下，根据SQL的执行顺序得知此子查询的结果返回的是一个标量。

# 子查询
SELECT *,(
	SELECT COUNT(employee_id)
	FROM employees e
	WHERE e.department_id = d.department_id) AS counts
FROM departments d;

当然，这个案例可以采用连接的方法，因为涉及两个表（employees和departments），且有公共字段（department_id），具体查询语句如下：

# 连接查询
SELECT d.*, COUNT(employee_id) AS counts
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`;

3. FROM后面的子查询

FROM后面一般是表，代表数据源，因此是把子查询的结果当作一张表，且必须给这张表起别名。

• 案例1：查询每个部门的平均工资的工资等级。

已知工资等级表（job_grades）：

# ①查询每个部门的平均工资
SELECT department_id,AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

根据平均工资在工资等级表的情况，可以判别每个部门平均工资的工资等级，这里就涉及非等值连接，判断平均工资是否介于某等级的最低工资和最高工资之间。

# ②查询工资等级，判断平均工资在哪个等级
SELECT avg_dep.*,grade_level
FROM (
	SELECT department_id,AVG(salary) AS avg_salary
	FROM employees
	GROUP BY department_id
) avg_dep
LEFT JOIN job_grades g
ON avg_salary BETWEEN lowest_sal AND highest_sal;

4. EXISTS后面的子查询

EXISTS后面的子查询也叫做相关子查询。

语法：EXISTS(完整的查询语句)，括号内的查询语句可以1行1列，也可以多行多列。

结果：1或0，当括号内有查询结果时，返回1，当括号内没有查询结果时，返回0。在逻辑判断中，1可认为是True，0可认为是False。

一般来讲，EXISTS后面的子查询都可以用IN后面的子查询代替，所以用得不是很多。

• 案例1：查询有员工的部门名。

# 采用IN的方式
SELECT department_name
FROM departments d
WHERE d.department_id IN (
	SELECT department_id
	FROM employees
);

# 采用EXISTS的方式
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT department_id
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
);

总结

（1）WHERE或HAVING后面的子查询结果可以是1行1列（标量），多行1列，或者1行多列，这里的子查询结果是作为筛选条件的，一般可使用单行运算符，比如<、>、=、<=、>=、!=，也可使用多行运算符，比如IN、ANY、SOME、ALL，常用的是IN。

（2）SELECT后面的子查询结果必须是1个标量，即1行1列，这里的子查询结果一般是生成新的字段。

（3）FROM后面的子查询结果一般是多行多列，充当一张表，且这张表必须取别名。

（4）EXISTS后面的子查询一般可以用IN后面的子查询代替。

                         相关和不相关子查询区别

转载自：https://zhuanlan.zhihu.com/p/350392183

前言

如果一个select语句能够返回单个值或者一列值，且该select语句嵌套在另一个SQL语句（例如select语句、insert语句、update语句或者delete语句）中，那么该select语句成为“子查询”（也叫内层查询），包含子查询的SQL语句称为“主查询”（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。子查询一般用在主查询的where子句或having子句中，与比较运算符或者逻辑运算符一起构成where筛选条件或having筛选条件。子查询分为“相关子查询”（Dependent Subquery）与“非相关子查询”。

非相关子查询

如果子查询返回单个值，则可以讲一个表达式的值与子查询的结果进行比较。 例如，检索成绩比学生张三平均分高的所有学生及课程的信息， 可以使用下面的SQL语句， 执行结果如下图。

mysql> select class_name, student.student_no, student_name, course_name, score
    -> from classes join student on student.class_no = classes.class_no
    -> join choose on choose.student_no = student.student_no
    -> join course on choose.course_no = course.course_no
    -> where score > (
    -> select avg(score)
    -> from student, choose
    -> where student.student_no = choose.student_no and student_name = '张三'
    -> );
复制代码

非相关子查询

说明

该示例中的子查询是一个单独的select语句，可以不依赖主查询单独运行。这种不依靠主查询，能够独立运行的子查询称为“非相关子查询”。

执行过程

1. 执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。

2. 执行外部查询，并显示整个结果。

相关子查询

下面的示例演示了相关子查询，代码第七行标记了两条子查询语句之间的区别（其他SQl代码完全相同），执行结果如下图。

mysql> select class_name, student.student_no, student_name, course_name, score
    -> from classes join student on student.class_no = classes.class_no
    -> join choose on choose.student_no = student.student_no
    -> join course on choose.course_no = course.course_no
    -> where score > (
    -> select avg(score)
    -> from choose
    -> where student.student_no = choose.student_no and student_name = '张三'
    -> );
复制代码

相关子查询

说明

从执行结果可以看到，子查询可以仅仅使用自己定义的数据源，也可以“直接引用”主查询中的数据源，但两者意义完全不同。

1. 如果子查询中仅仅使用了自己定义的数据源， 这种查询是非相关子查询。 非相关子查询是独立于外部查询的子查询， 子查询总共执行一次， 执行完毕后将值传递给主查询。

2. 如果子查询中使用了主查询的数据源， 这种查询是相关子查询， 此时主查询的执行与相关子查询的执行相互依赖。

执行过程

1. 从外层查询中取出一个元组，将元组相关列的值传递给内层查询。

2. 执行内层查询，得到子查询操作的值。

3. 外查询根据子查询返回的结果或结果集得到满足条件的行。

4. 然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

如何区分

说了这么多，那我们该如何快速区分非相关子查询和相关子查询呢？

最简单的办法的就是直接看子查询本身能否执行。比如执行上面的例子中的子查询：

mysql> select avg(score)
    -> from choose
    -> where student.student_no = choose.student_no and student_name = '张三';

子查询

会报错：1054 - Unknown column 'student.student_no' in 'where clause' 这样的查询语句构成的子查询便为相关子查询。

                                 子查询和表连接的对比

转载自：https://baijiahao.baidu.com/s?id=1760961516082640097&wfr=spider&for=pc

先说结论，由于执行逻辑和底层原理不同，一般情况下JOIN的查询效率远远高于相关子查询的，但是凡事儿都不能有绝对，接下来我们深入探讨一下。

在实际开发中，我们经常需要从多个表中获取数据，这时就需要使用多表查询。在多表查询时，常常会使用JOIN和相关子查询两种方式。那么，应该使用哪种方式呢？这是本文要探讨的问题。

一、JOIN和相关子查询的基本概念

1.JOIN

JOIN是一种多表查询的方式，它将两个或多个表连接在一起，以便我们可以从多个表中检索出所需的数据。JOIN可以分为内连接、左连接、右连接和全连接四种类型。在MySQL中，INNER JOIN和LEFT JOIN是最常用的两种JOIN方式。

INNER JOIN：它只返回两个表中共有的记录。这意味着只有在两个表中都存在的数据才会被返回。

LEFT JOIN：它返回左表中的所有记录以及右表中与左表匹配的记录。如果右表中没有匹配的记录，则会返回NULL值。

2.相关子查询

相关子查询是一种将子查询嵌套在主查询中的方式。在相关子查询中，内部查询的结果是由外部查询的一部分作为参数来决定的。相关子查询常常用于需要进行比较或计算的情况，例如在WHERE子句中使用IN或EXISTS操作符。

二、JOIN和相关子查询的优缺点

1.JOIN的优点

• 性能高效：JOIN是一种将多个表连接在一起的高效方式，它可以避免多次查询数据库。

• 可读性强：JOIN可以让SQL语句更容易理解和维护。它可以在单个查询中检索来自多个表的数据，从而使代码更加简洁和清晰。

2.JOIN的缺点

• 复杂性高：JOIN需要在查询中指定连接条件，这使得查询变得更加复杂。如果连接条件不正确，结果可能会返回错误的数据。

• 表的数量有限制：在使用JOIN时，连接的表的数量受到数据库性能的限制。如果连接的表数量过多，性能可能会变得很慢。

3.相关子查询的优点

• 简单易懂：相关子查询可以使用简单的语法来表示复杂的查询操作。这使得它更易于理解和维护。

• 适用范围广：相关子查询可以用于任何需要对数据进行比较或计算的场景。例如，在WHERE子句中使用IN或EXISTS操作符可以使用相关子查询。

4.相关子查询的缺点

• 性能较差：相关子查询的性能通常比JOIN要差。这是因为相关子查询需要在主查询返回结果之前先执行子查询。如果查询数据量很大，相关子查询的执行速度可能会很慢（2）嵌套深度有限制：相关子查询的嵌套深度是有限制的。如果子查询嵌套过多，会导致查询变得复杂且难以维护。

三、选择JOIN还是相关子查询

在选择使用JOIN还是相关子查询时，应该考虑以下几个因素：

• 性能：如果性能是最重要的考虑因素，那么应该使用JOIN。JOIN通常比相关子查询更快。

• 可读性：如果可读性是最重要的考虑因素，那么应该使用JOIN。JOIN可以让代码更加简洁和清晰。

• 查询的复杂度：如果查询比较简单，那么可以使用相关子查询。如果查询比较复杂，那么使用JOIN可能更加合适。

• 数据量：如果查询的数据量很大，那么应该使用JOIN。JOIN通常比相关子查询更适合处理大量数据。

在实际开发中，我们应该根据具体的情况选择使用JOIN还是相关子查询。如果查询比较简单且数据量不是很大，那么使用相关子查询可以使代码更加简洁和易于理解。如果查询比较复杂且需要处理大量数据，那么使用JOIN可能更加合适。

四、总结

本文探讨了在MySQL多表查询时使用JOIN还是相关子查询的问题。我们介绍了JOIN和相关子查询的基本概念、优缺点以及选择使用它们的因素。在实际开发中，我们应该根据具体的情况选择使用JOIN还是相关子查询，以便获得最佳的性能和可读性。