子查询使用

单行子查询

WHERE中使用子查询

# 查询工资大于149号员工工资的员工信息
SELECT  employee_id, last_name, salary
FROM employees
WHERE salary > (
    			SELECT salary
    			FROM employees
    			WHERE employee_id = 149
				);
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         100 | King      | 24000.00 |
|         101 | Kochhar   | 17000.00 |
|         102 | De Haan   | 17000.00 |
|         108 | Greenberg | 12000.00 |

HAVING中的子查询

# 查询最低工资大于110号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
					SELECT MIN(salary)
    				FROM employees
    				WHERE department_id = 110
					);
+---------------+-------------+
| department_id | MIN(salary) |
+---------------+-------------+
|            70 |    10000.00 |
|            90 |    17000.00 |
+---------------+-------------+

CASE中使用子查询

# 查询员工id，姓名，lacation
# 其中若员工department_id与location_id为1800的department_id相同
# 则lacation为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
    ) AS "location"
FROM employees;
+-------------+-------------+----------+
| employee_id | last_name   | location |
+-------------+-------------+----------+
|         100 | King        | USA      |
|         101 | Kochhar     | USA      |
|         102 | De Haan     | USA      |

多行子查询

多行比较符

IN：等于列表中的任意一个
ANY：和子查询返回的某一个值比较
ALL：和子查询返回的所有值比较

# 返回其他job_id中比job_id为IT_PROG部门任一工资低的员工的员工信息
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'
				);
+-------------+-------------+------------+---------+
| employee_id | last_name   | job_id     | salary  |
+-------------+-------------+------------+---------+
|         110 | Chen        | FI_ACCOUNT | 8200.00 |
|         111 | Sciarra     | FI_ACCOUNT | 7700.00 |
|         112 | Urman       | FI_ACCOUNT | 7800.00 |
|         113 | Popp        | FI_ACCOUNT | 6900.00 |
|         115 | Khoo        | PU_CLERK   | 3100.00 |
|         116 | Baida       | PU_CLERK   | 2900.00 |

基本相关子查询

每执行一次外部查询，子查询都要重新计算一次，这样的查询称为相关子查询

# 查询员工id，salary，按照department_name排序
SELECT employee_id, salary
FROM employees e
ORDER BY (
		SELECT department_name
    	FROM departments d
    	WHERE e.department_id = d.department_id
		) DESC;
+-------------+----------+
| employee_id | salary   |
+-------------+----------+
|         120 |  8000.00 |
|         121 |  8200.00 |
|         122 |  7900.00 |

EXISTS/NOT EXISTS关键字

将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

EXISTS

# 查询公司管理者的信息
SELECT employee_id,last_name,job_id, department_id
FROM employees e1
WHERE EXISTS (
    		SELECT *
    		FROM employees e2
    		WHERE e1.employee_id = e2.manager_id
			);
+-------------+-----------+---------+---------------+
| employee_id | last_name | job_id  | department_id |
+-------------+-----------+---------+---------------+
|         100 | King      | AD_PRES |            90 |
|         101 | Kochhar   | AD_VP   |            90 |
|         102 | De Haan   | AD_VP   |            90 |
|         103 | Hunold    | IT_PROG |            60 |
|         108 | Greenberg | FI_MGR  |           100 |

NOT EXISTS

# 查询departments表中，不存在于employees表中的部门id，部门名称
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
    		SELECT *
    		FROM employees e
    		WHERE d.department_id = e.department_id
			);
+---------------+----------------------+
| department_id | department_name      |
+---------------+----------------------+
|           120 | Treasury             |
|           130 | Corporate Tax        |
|           140 | Control And Credit   |
|           150 | Shareholder Services |
|           160 | Benefits             |

结论：

在除了GROUP BY、LIMIT之外，其他位置可以使用子查询
更推荐多表连接（多表连接的优化更好）

Dawn's Blogs

MySQL基础 (8) 子查询

子查询使用

单行子查询

多行子查询

相关子查询

基本相关子查询

EXISTS/NOT EXISTS关键字

EXISTS

NOT EXISTS