1.子查询:子查询要包含在括号内,将子查询放在比较条件的右侧增强可读性(子查询可以出现在比较运算符的两侧),单行操作符对应单行子查询,多行操作符对应多行子查询
SQL> select last_name,salary from employees where salary > (select salary from employees where last_name = 'Abel'); LAST_NAME SALARY ------------------------- ---------- Hartstein 13000 Higgins 12008 King 24000 Kochhar 17000 De Haan 17000 Greenberg 12008 Russell 14000 Partners 13500 Errazuriz 12000 Ozer 11500 10 rows selected.
2.在子查询中使用组函数:
SQL> SELECT last_name, job_id, salary FROM employees WHERE salary = 2 3 4 (SELECT MIN(salary) FROM employees); 5 LAST_NAME JOB_ID SALARY ------------------------- ---------- ---------- Olson ST_CLERK 2100
3.子查询中的 HAVING 子句:首先执行子查询,向主查询中的 HAVING 子句返回结果:
SQL> SELECT department_id, MIN(salary) FROM employees 2 GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees 3 WHERE department_id = 50); DEPARTMENT_ID MIN(SALARY) ------------- ----------- 100 6900 30 2500 7000 20 6000 70 10000 90 17000 110 8300 40 6500 80 6100 10 4400 60 4200 11 rows selected.
4.子查询中的空值问题,子查询不返回任何行,因为员工表没有“Haas”:
SQL> SELECT last_name, job_id FROM employees 2 WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); no rows selected