子查询

分类

• 单行子查询

• 多行子查询

• 相关子查询

• 不相关子查询

单行子查询

单行操作符：>, <, =, !=, <=, >=

多行子查询

操作符	含义
IN	等于列表中任意一个
ANY	和单行操作符一起使用，和子查询返回的某一个值比较
ALL	和单行操作符一起使用，和子查询返回的所有值比较
SOME	等同于ANY

#ANY
#eg.查询其他job_id中比job_id为`IT_PROG`部门任一工资低的员工的姓名，job_id，salary
SELECT last_name,job_id,salary
FROM employees e
WHERE job_id != 'IT_PROG'
AND salary < ANY(
		SELECT salary
		FROM employees e2
		WHERE job_id = 'IT_PROG'
		);

#ALL:
#eg.查询平均工资最低的部门id
SELECT AVG(salary), department_id
FROM employees e
GROUP BY department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) avg_sal
			FROM employees e
			GROUP BY department_id
			);

注：若多行查询结果存在NULL，则查询结果为空

相关子查询

依赖于外部查询，通常情况下是因为子查询用到了外部的表，并进行条件关联，每执行一次外部查询，子查询都要重新计算一次。

#eg1.查询工资高于本部门平均工资的员工的last_name,salary,department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE e2.department_id  = e1.department_id 
		);

#eg2.查询员工的employee_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
	 ) ASC;

SELECT DISTINCT xxx      
FROM xxx JOIN xxx ON ccc 
WHERE ccc                
GROUP BY xxx             
HAVING xxx               
ORDER BY xxx DESC		 
LIMIT xxx                

SELECT中除了GROUP BY和LIMIT，其他位置后面都可以声明子查询

常配合EXISTS, NOT EXISTS使用，检查在子查询中是否存在满足条件的行

#eg.查询管理者的employee_id, last_name, department_id
SELECT employee_id, last_name,department_id
FROM employees e1
WHERE EXISTS (
	      SELECT * #找到记录即可，具体字段无需知道
	      FROM employees e2
	      WHERE e1.employee_id = e2.manager_id
	      );