SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
运算符 | 含义 |
IN | 与列表中的任一成员相等 |
ANY | 与子查询返回的每一个值比较 |
ALL | 与子查询返回的所有值比较 |
eg: 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';
eg: SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
子查询中的空值
相关子查询
SELECT column1,column2,... FROM table1 outer WHERE column1 operater (SELECT column1,column2 FROM table2 WHERE expr1 = outer.expr2);
子查询参考父查询表中的一列
使用相关子查询
找到那些收入超过他们部门平均薪酬的所有雇员
SELECT last_name,salary,department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id);
每次从外部查询来的行被处理的时候,内部查询就被求值
显示那些至少已经更换了两次工作的雇员
SELECT e.employee_id,e.last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);
— 内部查询中的搜索将不会继续
— 条件将会标记为TRUE
— 条件将会标记为FALSE
— 内部查询中的搜索将会继续
找到那些至少有一个人向他们报告的雇员
SELECT employee_id , last_name,job_id,department_id FROM employees outer WHERE EXISTS (SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);
找到没有任何职员的部门
SELECT department_id,department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id);