Oracle-007-19:Advanced Subqueries[高级子查询]

在where、order by、select、having子句中使用子查询。
1.标量子查询，准确值查询
2.多值匹配子查询
3.在case when、decode等中使用子查询
4.关联子查询
5.exists和not exists与in和not in的转化关系
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 employee_id,last_name,job_id,department_id FROM employees
WHERE employee_id IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);
有没有not null没有关系，可以参考前面介绍的带in的子查询
SELECT department_id, department_name FROM departments d
WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id);
<=>
SELECT department_id, department_name FROM departments
WHERE department_id NOT IN (SELECT department_id FROM employees);
NOT IN可以和NOT EXISTS互换，但是两者之间有一个很大的差别，就是当子查询的数据中包含有NULL时，NOT IN的结果为FALSE。
当子查询中有一个null的时候，整个查询结果为null，参考前面带not in的子查询说明
6.带子查询的update语句注意问题：
update empx t set t.department_name=(select department_name from departments s where t.department_id=s.department_id)为107行更改记录，可能更改的只有匹配的104行记录
带条件约束
update empx t set t.department_name=(select department_name from departments s where t.department_id=s.department_id) where exists (select 1 from departments x where t.department_id=x.department_id)为104行记录。
量子查询：每一行精确的返回一列
可以用于：Decode和Case子句中的表达式,select语句中的所有子句，除了group by
如果标量子查询没有找到匹配的行，那么标量子查询的值为NULL；如果返回了不止一行，那么Oracle服务器返回错误。
标量子查询不能在下列情况使用：
Group by, check约束, when条件, Having子句, start with, connect by以及和查询不相关的语句，如create profile
相关子查询用于一行一行的处理，针对外查询的每一行子查询都会执行一次。
例如下面的例子，子查询引用了外查询的一列：
此时一定要给外查询起个别名，否则子查询会无法区别两个表。
可以在相关子查询中使用ALL或者ANY运算符
相关子查询用于更新表：
with子句：当一个复杂的查询中要包含同一个查询块多次时，可以使用with子句
with子句取得查询块的结果并存储在用户的临时表空间，使用with子句可以提高性能。
with子句只在select语句中使用，对查询所起的别名对于其后的所有语句都可见，如果后面有重复的表名，那么查询所起的别名会覆盖掉表名。
在with子句中可以包含不止一个查询，每个查询之间以','分隔。

举例：
select last_name,department_id,salary from employees where commission_pct is not null包括department_id或salary为null的值
不<=>
1.select last_name,department_id,salary from employees where (department_id,salary) in (select department_id,salary from employees where commission_pct is not null)不包括department_id或salary为null的值