Mysql基础归纳--第六卷（子查询详细）

子查询

举个例子，自连接和子查询分别对上述问题的解决。

#：自连接 SELECT e2.last_name,e2.salary 
FROM employees e1,employees e2 
WHERE e1.last_name = 'Abel' AND e1.`salary` < e2.`salary`


#：子查询
SELECT last_name FROM employees 
WHERE salary > 
( SELECT salary FROM employees WHERE last_name = 'Abel' );

子查询的基本使用

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。

注意事项

• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询

1.单行子查询

我们按内查询的结果返回一条叫 单行子查询 

单行比较操作符：

代码示例1：

#返回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);

代码示例2：

题目：查询与141号或174号员工的manager_id和department_id（这是子查询要用到的）相同的其他员工的employee_id， manager_id，department_id（主查询）

实现1：不成对比较：即子查询两个条件是分开的，正如下方子查询用AND连接了。
SELECT employee_id, manager_id, department_id FROM employees 
WHERE manager_id IN 
(SELECT manager_id FROM employees WHERE employee_id IN (174,141)) 

AND
 
department_id IN 
(SELECT department_id FROM employees WHERE employee_id IN (174,141)) 
AND 

employee_id NOT IN(174,141);

#实现2：成对比较

SELECT employee_id, manager_id, department_id 
FROM employees 
WHERE (manager_id, department_id) IN 
           (SELECT manager_id, department_id 
            FROM employees WHERE employee_id IN (141,174)) 
AND employee_id NOT IN (141,174);

题目：查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资

SELECT department_id, MIN(salary) FROM employees 
GROUP BY department_id HAVING MIN(salary) >   #主查询有聚合函数，后面必定要有groupby 
(SELECT MIN(salary) FROM employees WHERE department_id = 50);

1.1 case中的子查询

题目：显式员工的 employee_id,last_name 和 location （主查询） 。其中，若员工 department_id 与 location_id 为 1800 的 department_id 相同 (这是switch case的判断语句) ，则 location 为 ’Canada’ ，其余则为 ’USA （“若则”就是case when.. Then）

Select  employee_id, last_name,
(CASE department_id
WHEN  (SELECT department_id departments WHERE location_id=1800)
THEN 'Canada' ELSE 'USA' END) location
FROM  employees

1.2 子查询的空值问题

SELECT last_name, job_id FROM employees 
WHERE job_id = 
(SELECT job_id FROM employees WHERE last_name = 'Haas');

这个子查询不返回任何行

1.3 非法使用子查询

SELECT employee_id, last_name FROM employees 
WHERE salary 
= (SELECT MIN(salary) FROM employees GROUP BY department_id);

这个子查询没个部门返回各部门最少工资，所以是多条记录；而条件查询where中，接收的salary只能值为一个。

 2.多行子查询

也称为集合比较子查询

内查询返回多行

使用多行比较操作符

 2.1 多行比价操作符

 2.2代码示范

题目1：返回其它job_id中（主查询的列）比job_id为‘IT_PROG’部门任一工资低的员工（这个是子查询）的员工号、姓名、job_id 以及salary（主查询的列）

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'

 题目2：查询平均工资最低的部门id

#方式1： SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) = ( 
       SELECT MIN(avg_sal) FROM (  #这里的from后面是由子查询返回的结果集形成的dept_avg_sal表
            SELECT AVG(salary) avg_sal 
            FROM employees GROUP BY department_id 
                    ) dept_avg_sal 
)

#方式2：用了all，而且也是配合了单行操作符使用的，返回的是多个结果进行单行比较
SELECT department_id 
FROM employees GROUP BY 
department_id 
HAVING AVG(salary) <= ALL (
                   SELECT AVG(salary) avg_sal 
                   FROM employees 
                   GROUP BY department_id )

3.相关子查询

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

注意：相关子查询的话，一般表明要命别名使用。

 3.1代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

#相关子查询
SELECT 的last_name,salary,department_id
FROM employee outer 
WHERE salary>
           (SELECT AVG(salary) 
           FROM employees
           WHERE department_id=outer.department_id
            )

#在from中使用子查询
SELECT last_name,salary,e1.department_id FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2 
WHERE e1.`department_id` = e2.department_id AND e2.dept_avg_sal < e1.`salary`;

在orderby里面用相关子查询

题目：查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary FROM employees outer 
ORDER BY ( 
          SELECT department_name FROM departments d 
          WHERE outer.`department_id` = d.`department_id` 
);

3.2EXIST和 NOT EXIST关键字

关联子查询通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行：

条件返回 FALSE

继续在子查询中查找

如果在子查询中存在满足条件的行：

不在子查询中继续查找

条件返回 TRUE

NOT EXISTS 关键字表示如果不存在某种条件，则返回 TRUE ，否则返回 FALSE 。

题目： 查询公司管理者的 employee_id ， last_name ， job_id ， department_id 信息

SELECT employee_id, last_name, job_id, department_id 
FROM employees e1 
WHERE EXISTS ( SELECT * FROM 
             employees e2 
             WHERE e2.manager_id = e1.employee_id
);