二、 单行子查询
2.1 单行比较操作符
2.2 代码示例
题目：查询工资大于149号员工工资的员工的信息
题目：返回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);
题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
实现方式1：不成对比较
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);
2.3 HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
题目：查询最低工资大于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);
2.4 CASE中的子查询
在CASE表达式中使用单列子查询：
题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id, last_name, (CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location FROM employees;
2.5 子查询中的空值问题
SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');
子查询不返回任何行
2.5 非法使用子查询
SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
多行子查询使用单行比较符
