【MySQL】子查询，合并查询

1.子查询

1.1.单行子查询

1. 2.多行子查询

1.3.多列子查询

1.4.在from子句里面使用子查询

2.合并查询

1.子查询

子查询，也称为嵌套查询或子选择，是SELECT嵌入在另一个SQL查询的 WHERE 或 HAVING子句中的查询。子查询返回的数据由外部语句使用，与使用文字值的方式相同。

子查询提供了一种简单而有效的方法来处理依赖于另一个查询结果的查询。它们几乎与普通的SELECT语句相同，但几乎没有限制。

括号内的子查询：

子查询必须始终出现在括号内，以明确区分它们与外部查询的其余部分。

单列限制：

子查询在大多数情况下需要只返回一列数据。这是因为在很多情况下，子查询的结果需要与外部查询中的某个列进行比较。
如果表确实只有一列，那么理论上可以使用SELECT *，但通常出于清晰和明确性的考虑，最好还是指定具体的列名。
如果需要比较多列（即行比较），则子查询可以返回多列，但外部查询也需要相应地调整以匹配这些列。

多值运算符：

当子查询返回多行时，通常需要使用多值运算符（如IN、NOT IN、ANY、ALL等）来与外部查询进行比较。
例如，SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York') 会返回所有在纽约工作的部门的员工。

UNION的限制：

子查询不能使用UNION来合并多个SELECT语句的结果。每个子查询只能包含一个SELECT语句。
如果需要合并多个查询的结果，通常需要在外部查询中执行UNION，而不是在子查询中。

广泛的应用场景：

子查询最常与SELECT语句一起使用，但是也可以在INSERT，UPDATE或DELETE语句中或在另一个子查询中使用它们。
例如，UPDATE employees SET salary = salary * 1.1 WHERE employee_id IN (SELECT employee_id FROM bonuses WHERE bonus_amount > 5000) 会将所有奖金超过5000元的员工的薪资提高10%。

1.1.单行子查询

单行子查询必须只返回一个值。如果子查询返回多行，数据库系统会报错，因为外层查询期望的是一个单一的值来进行比较或操作。

sql">SELECT column_name  
FROM table_name  
WHERE column_name = (SELECT single_value FROM another_table WHERE condition);

SELECT column_name：这指定了外部查询要从table_name中选择的列。
FROM table_name：这指定了外部查询的表。
WHERE column_name =：这是一个条件，它指定了外部查询的过滤条件。这里的条件是要检查的列等于子查询返回的值。
(SELECT single_value FROM another_table WHERE condition)：这是子查询。它从another_table中选择一个单一的值（single_value），这个值是根据WHERE子句中的condition来确定的。

子查询的关键点是它返回一个单一的值，这个值可以被外部查询直接使用。如果子查询返回多于一个值，那么SQL数据库将会抛出一个错误，因为外部查询期望的是一个单一的比较值。

显示SMITH同一部门的员工

我们可以先找到SMITH的部门
然后按照SMITH的部门进行条件筛选。

sql">select ename, deptno from emp where deptno=(select deptno from emp where ename='SMITH');

1. 2.多行子查询

多行子查询是指子查询返回的结果集中包含多行数据。与单行子查询（只返回一行数据）相比，多行子查询更加灵活，因为它可以处理多个值的情况。

多行子查询的SQL语法如下：

sql">SELECT column_name 
FROM table_name 
WHERE column_name 
IN (SELECT single_column FROM another_table WHERE condition);

SELECT column_name：这是主查询的选择列表，指定了您希望从table_name中检索的列。
FROM table_name：这指定了主查询的目标表，即您希望从中检索数据的表。
WHERE column_name IN：这是主查询的过滤条件。IN运算符用于检查column_name的值是否存在于子查询返回的结果集中。
(SELECT single_column FROM another_table WHERE condition)：这是子查询，它从another_table中选择single_column列的值，这些值是根据WHERE子句中的condition来确定的。子查询的结果集是一个值列表，主查询将使用这个列表来过滤结果。

需要注意的是，尽管这里子查询被命名为“选择单个列”（single_column），但实际上子查询可以返回多行数据，只要这些数据都是single_column列中的值。IN运算符会检查主查询中的column_name是否匹配子查询结果集中的任何一个值。

2.1 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

首先理解题意：10号部门中有许多的工作岗位，我们需要在整张表中去筛选员工的工作岗位和10号部门中的工作岗位是相同的，最后我们还要去掉处于10号部门中的人。

首先我们可以先使用select将10号部门中的工作岗位全部筛选出来。
然后我们可以在where条件中，将第一步返回的结果作为一个筛选条件，由于第一步返回的是一张多行的表格，而我们只需要满足其中的一个条件就满足了要求，所以这里我们可以使用in关键字（****）
最后我们再去除隶属于10号部门的员工就完成了题目要求。

sql">select ename,job,sal,deptno from emp where (job in (select job from emp where deptno=10)) and (deptno != 10);

SELECT ename, job, sal, deptno：这是主查询的选择列表，指定了您希望从emp表中检索的列，即员工姓名（ename）、职位（job）、薪资（sal）和部门编号（deptno）。
FROM emp：这指定了主查询的目标表，即您希望从中检索数据的表。
WHERE子句包含两个条件，它们通过AND运算符组合：

(job IN (SELECT job FROM emp WHERE deptno = 10))：这个条件使用IN运算符和子查询来检查主查询中的job列的值是否出现在子查询返回的结果集中。子查询从emp表中选择部门编号为10的所有不同职位。如果主查询中的某行的job值在子查询的结果集中，那么这个条件就为真。
(deptno != 10)：这个条件直接检查主查询中的deptno列的值是否不等于10。如果某行的部门编号不是10，那么这个条件就为真。

只有当这两个条件同时满足时，主查询中的行才会被包含在结果集中。换句话说，这个查询返回的是那些具有部门10中至少一个职位但自己并不在部门10中的员工的信息。

2.2.显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号

方法一：使用单行子查询

比30号部门的所有员工的工资高，这句话意味着只要我们比30号部门的最高工资高我们就能够满足题目要求。
所以我们可以直接使用聚合函数max完成我们的要求，当然使用max其实意味着我们使用一个单行子查询就能完成我们要求。

sql">select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno=30);

方法二：多行子查询

我们就按题目的要求，要比30号部门的所有员工的工资高。

我们可以先拿到30号部门的所有工资。
然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张多行的表格，而我们需要满足所有条件才能满足了要求，所以这里我们可以使用all关键字（表示满足其中的所有的条件，才成立）
最后按照题目的要求拿到：员工的姓名、工资和部门号。

sql">select ename, sal, deptno from emp where sal > all (select sal from emp where deptno=30);

SELECT ename, sal, deptno：这是主查询的选择列表，指定了您希望从emp表中检索的列，即员工姓名（ename）、薪资（sal）和部门编号（deptno）。
FROM emp：这指定了主查询的目标表，即您希望从中检索数据的表。
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)：这是主查询的过滤条件。ALL运算符用于检查主查询中的sal（薪资）列的值是否大于子查询返回的所有薪资值。
子查询SELECT sal FROM emp WHERE deptno = 30从emp表中选择所有部门编号为30的员工的薪资。
主查询然后检查其sal列的值是否大于子查询返回的所有薪资值。如果主查询中的某行满足这个条件（即其薪资高于部门30中所有员工的薪资），那么这行就会被包含在结果集中。

需要注意的是，ALL运算符在这里要求主查询中的薪资值必须大于子查询返回的每一个薪资值

2.3 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

和上面的题目一样，第一种方法我们直接找30号部门的最低工资，当然这里为了介绍any关键字，我们采用多行子查询的方式进行解决这个问题。

我们可以先拿到30号部门的所有工资。
然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张多行的表格，而我们只需要满足任意一个条件就能满足要求，所以这里我们可以使用any关键字（表示满足其中的任意一个条件，就能成立）
最后按照题目的要求拿到：员工的姓名、工资和部门号。

sql">select ename, sal,deptno from emp where sal > any (select distinct sal from emp where deptno=30);

SELECT ename, sal, deptno：这是主查询的选择列表，指定了您希望从emp表中检索的列，即员工姓名（ename）、薪资（sal）和部门编号（deptno）。
FROM emp：这指定了主查询的目标表，即您希望从中检索数据的表。
WHERE sal > ANY (SELECT DISTINCT sal FROM emp WHERE deptno = 30)：这是主查询的过滤条件。ANY运算符用于检查主查询中的sal（薪资）列的值是否大于子查询返回的任意一个薪资值。

子查询SELECT DISTINCT sal FROM emp WHERE deptno = 30从emp表中选择部门编号为30的所有不同（唯一）的薪资值。DISTINCT关键字确保子查询返回的结果集中没有重复的薪资值。
主查询然后检查其sal列的值是否大于子查询返回的任意一个薪资值。如果主查询中的某行满足这个条件（即其薪资高于部门30中任意一个员工的薪资），那么这行就会被包含在结果集中。

与ALL运算符不同，ANY运算符要求主查询中的薪资值只需大于子查询返回的薪资值集合中的任意一个值即可。

in和any的区别

in关键字：

in关键字用于检查某个值是否存在于子查询返回的结果集中，如果子查询返回多个结果，in关键字会检查列的值是否匹配子查询中的任何一个结果。

any关键字：

any关键字用于与比较运算符一起使用，以比较某个值与子查询返回的任何一个结果。

总结：

in关键字是检查值是否存在于子查询的结果集中。
any关键字是用于与比较运算符一起使用，以比较值与子查询的任何一个结果。

例如上面笔者给的in与any的练习中，你会发现in与any是不能相互替换的。

1.3.多列子查询

单行子查询是指子查询只返回单行单列数据，多行子查询是指返回单列多行数据，然而它们都是针对单列而言，而多列子查询则是指查询返回多个列数据的子查询语句。

3.1 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

显然我们可以先使用子查询先找到SMITH的部门和岗位。
然后将第一步返回的结果作为一个where筛选条件，由于第一步返回的是一张单行多列的表格，而我们又需要同时比较两个条件，（其他人的部门和岗位要与SMITH完全相同），所以这里我们可以对要比较的数据加上一个()中间使用，分割要比较的字段，表示一次比较多个字段。

sql">select ename, job,deptno from emp where (job,deptno)= (select job,deptno from emp where ename='SMITH') and ename != 'SMITH';

这里的关键在于使用了多列的比较。在WHERE子句中，您通过元组比较(job,deptno) = (select job,deptno from emp where ename='SMITH')来确保所选的员工与名为'SMITH'的员工在职位和部门上相匹配。同时，通过and ename != 'SMITH'来排除名为'SMITH'的员工本身。

这个查询的执行过程如下：

子查询(select job,deptno from emp where ename='SMITH')首先执行，返回名为'SMITH'的员工的job,deptno （职位和部门编号）。
主查询接着执行，它检查emp表中的每一行，看看哪些行的job,deptno（职位和部门编号）与子查询返回的结果相匹配。
对于那些匹配的行，主查询进一步检查ename字段的值是否不等于'SMITH'。
最终，主查询返回所有满足这些条件的员工的ename（名字）、job（职位）和deptno（部门编号）。

说明一下：

多列子查询得到的结果是多列数据，在比较多列数据时需要将待比较的多个列用圆括号括起来。
多列子查询返回的如果是多行数据，在筛选数据时也可以使用in、all和any关键字。

1.4.在from子句里面使用子查询

目前我们练习和使用的子查询，我们全都在where子句中，充当判断条件用的。
此外子查询还能够在form子句中使用，用于充当一个子表。

子查询返回的结果集可以看作是一个临时表，可以用在FROM子句中，通常结合一个别名来使用。

sql">SELECT t1.column_name, t2.column_name  
FROM (SELECT column1, column2 FROM table_name WHERE condition) AS t1  
JOIN another_table AS t2 ON t1.common_column = t2.common_column;

下面是对这个查询的详细解释：

子查询（内联视图）：

子查询部分：(SELECT column1, column2 FROM table_name WHERE condition)
这个子查询从table_name表中选择column1和column2两列，但仅选择满足condition条件的行。
子查询的结果被赋予一个别名t1，这样它就可以在主查询中被引用。

表连接（JOIN）：

主查询部分：SELECT t1.column_name, t2.column_name FROM (...) AS t1 JOIN another_table AS t2 ON t1.common_column = t2.common_column;
在这个主查询中，我们选择了两个列：t1.column_name（来自子查询的结果）和t2.column_name（来自另一个表another_table）。
表another_table也被赋予了一个别名t2，以便在查询中引用。

连接条件：

ON t1.common_column = t2.common_column 指定了如何将t1和t2连接起来。这里，它基于两个表中都有的一个共同列common_column来连接行。

注意：

在SELECT子句中引用的t1.column_name和t2.column_name应该是明确的，即它们应该分别来自t1（子查询的结果）和t2（another_table表）。但是，在实际查询中，您可能需要替换为实际的列名，因为column_name在这里是一个占位符。
condition应该是一个有效的SQL条件表达式，用于筛选table_name中的行。
common_column是两个表中用于连接的共同列的名称。

查询的执行：

数据库首先执行子查询，生成一个临时结果集（或称为内联视图），该结果集包含满足条件的行和选定的列。
然后，数据库执行主查询，将子查询的结果集（t1）与another_table（t2）连接起来，基于指定的连接条件。
最后，数据库返回满足所有条件的行，包括在SELECT子句中指定的列。

4.1.显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

首先我们可以先将每一个部门的的平均工资求取出来。

sql">select deptno, avg(sal) from emp group by deptno;

然后我们可以将我们的员工表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了员工和平均工资组合的所有可能性。

sql">select * from emp,(select deptno, avg(sal) from emp group by deptno) as tmp;

但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是员工表和临时表的部门号emp.deptno = tmp.deptno必须相同。

sql">select * from emp,(select deptno, avg(sal) from emp group by deptno) as tmp where emp.deptno = tmp.deptno;

最后按照题目要求，我们必须在where条件中进行筛选员工的工资要高于平均工资sal > avg_sal

sql">select ename, emp.deptno, sal, avg_sal from emp, (select deptno, avg(sal) avg_sal from emp group by deptno) as tmp where (emp.deptno=tmp.deptneptno) and (sal > avg_sal);

说明：在from子句中使用子查询时，必须给子查询得到的临时表取一个别名，否则查询将会出错。

4.2 显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

每个部门最高的工资，显然我们要分组然后使用max求出。

sql">select deptno,max(sal) max_sal from emp group by deptno;

然后将员工表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了员工和最高工资组合的所有可能性。

sql">select * from emp,(select deptno,max(sal) max_sal from emp group by deptno) as tmp;

但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是员工表和临时表的部门号emp.deptno = tmp.deptno必须相同。

sql">select * from emp,(select deptno,max(sal) max_sal from emp group by deptno) as tmp where emp.deptno = tmp.deptno;

最后按照题目要求，我们必须在where条件中进行筛选员工的工资要等于最高工资sal = max_sal

sql">select ename, sal, emp.deptno, max_sal from emp, (select deptno,max(sal) max_sal from emp group by deptno) as tmp where (emp.deptno=tmp.deptno) and (sal = max_sal);

4.3 显示每个部门的信息（部门名，编号，地址）和人员数量

显然每个部门人员的数量我们要使用分组聚合进行统计。

sql">select deptno, count(*) total from emp group by deptno;

然后将部门表与第一步得到的临时表进行笛卡尔积，这样我们就拿到了部门信息和人员数量组合的所有可能性。

sql">select * from dept,(select deptno, count(*) total from emp group by deptno) as tmp;

但是有些组合是不可能的，所以我们需要对笛卡尔积的结果进行过滤，过滤的条件是部门表和临时表的部门号dept.deptno = tmp.deptno必须相同。

sql">select * from dept,(select deptno, count(*) total from emp group by deptno) as tmp where dept.deptno = tmp.deptno;

最后按照题目要求，我们筛选出部门名，编号，地址和人员数量。

sql">select dname,dept.deptno,loc,total from dept, (select deptno, count(*) total from emp group by deptno) as tmp where dept.deptno=tmp.deptno;

学习完这些复合查询以后我们可以体会到：

解决多表问题的本质: 就是想办法将多表转化成为单表，所以sql>mysql中，所有select的问题，全部都可以转成单表问题!

2.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

union all用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。
union也用于取得两个查询结果的并集，union会自动去掉结果集中的重复行。

2.1 显示工资大于2500或职位是MANAGER的员工

对于这个需求，我们使用where与or运算符很容易解决，当然这里为了介绍合并查询我们这里使用两条SQL语句：

显示工资大于2500的员工

sql">select * from emp where sal > 2500;

显示职位是MANAGER的员工

sql">select * from emp where job='MANAGER';

这里为了满足题目的要求我们我们使用union all将两条SQL连接起来：

sql">select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

结果确实是我们想要的，但是我们会发现结果中有重复的，因为有员工工资即大于2500,又是MANAGER

因此这里我们可以使用union，union会自动去掉结果集中的重复行。

说明：

待合并的两个查询结果的列的数量必须一致，否则无法合并。
待合并的两个查询结果对应的列属性可以不一样，但不建议这样做。