前言
子查询分类
分类方式1
单行子查询和多行子查询
分类方式2
相关子查询和不相关子查询
一、实际需求解决
我们先从一个实际问题入手
谁的工资比Abel高，且Abel的工资是多少？
分解一下
Abel的工资：
SELECT last_name,salary FROM employees WHERE last_name = "Abel";
谁的工资比Abel高:
SELECT last_name,salary FROM employees WHERE salary > 11000;
1.方式1：自连接
SELECT e2.last_name,e2.salary FROM employees e1 ,employees e2 WHERE e2.salary > e1.salary AND e1.last_name = "Abel";
自连接并不能解决所有问题
2.方式2：子查询
更加通用
SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = "Abel" );
二、单行子查询
1.操作符子查询
查询工资大于149号员工工资的员工的信息
SELECT employee_id,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE employee_id = 149 );
返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT employee_id,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE employee_id = 143 ) AND job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 );
返回公司工资最少的员工
SELECT employee_id,salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees ); 查询与141号员工的manager_id 和 department_id相同的其他员工 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 );
我们可以看到上面还包含了141这个人，明显结果不可能有这个
我们加个不等于<>
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;
这个题目还有另外一个写的方式
这个方法的使用场景比较少
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;
三、相关子查询
查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );
四、自定义语句
题目：显式员工的employee_id,last_name和location
其中，若员工department_id与location_id为1800的department_id相同
则location为'China'，区域的为'USA'。
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN "China" ELSE "USA" END "location" FROM employees;
五、子查询的问题
1.空值问题
如果语句逻辑没有问题
可能本身子查询就没有返回值
SELECT last_name,job_id FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE last_name = "haas" );
排错需要先看子查询是否有值
SELECT job_id FROM employees WHERE last_name = "haas"
本身没有返回值，所以为空是正常的
2.非法使用子查询
SELECT employee_id,last_name FROM employees WHERE SALARY = ( SELECT MIN(salary) FROM employees GROUP BY department_id);
这里的错误是因为“=”是一个单行子查询，但这里子查询返回的结果是一堆元组，所以就会有报错的问题
六、多行子查询
前面那个逻辑错误是单行子查询的逻辑错误
我们解决这个问题可以使用多行子查询的方法
我们把上面那个逻辑错误的修改一下
SELECT employee_id,last_name FROM employees WHERE SALARY in ( SELECT MIN(salary) FROM employees GROUP BY department_id);
返回其他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" );
返回其他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" );
