嵌套子查询subquery
单行子查询 select select_list from table where expr operator (select select_list from table); operator includes a comparison condition such as >, =, or IN
如： select last_name,hire_date from employees where hire_date > (select hire_date from employees where last_name = 'Davies'); [可选] and ID=(select .......);
如: select A from table group by C having min(column)>(select ......); 当子查询有group by 时，其输出行数只能是1行；
多行子查询 IN , ANY , ALL 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'; 说明： <ANY means less than the maximum. >ANY means more than the minimum. =ANY is equivalent to IN.
多列子查询 Syntax: SELECT column, column, ... FROM table WHERE (column, column, ...) IN (SELECT column, column, ... FROM table WHERE condition); 如： select first_name,dapartment_id,salary from employees where (salary,department_id) IN (select min(salary),department_id from employees group by department_id) order by department_id;
空值子查询 子查询语句要把NULL处理掉才有结果 SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);
