--subquery子查询


--single-row subqueries 单行子查询
Select Sysdate From dual;
--multiple-row subqueries 多行子查询
Create Table t As Select * From tt;
Select * From tt Where id In (Select Distinct Id From t);
--any
Select * From tt Where id > Any (Select Distinct Id From t);
Select * From tt Where id > (Select Min(Id) From t);
--multiple-column subquery多列子查询
Select * From t Where (Id,Name) In (Select Id,Name From tt);
Insert Into t Select Id,(Select 2000 From dual) From tt;
--======================
--相关子查询
Select t.Id,t.Name,t.name1 From t Where Id > (Select Avg(Id) From t);
--相关子查询 配合update,delete使用
Select * From t;
Update t a Set a.Name= 'bigger than avg' Where a.Id > (Select Avg(Id) From t Where t.Id = a.Id);
--exists
Select * From t Where Exists(Select 1 From tt Where tt.Id = t.Id);  --exists为true的行返回
/*
exists和in的比较，2个表一样大小时，差别不大
子表大时，用exists，子表小时，用in
*/
Create Table t2 As Select * From dba_objects
Create Table t3 As Select * From DBA_STORED_SETTINGS
Select Count(*) From t2 Where object_id In (Select object_id From t3);                             --set timing on;   0.031
Select Count(*) From t2 Where Exists (Select 1 From t3 Where t2.object_id = t3.object_id);         --set timing on;   0.031
Select /*+ ordered use_nl(t2 t3) */ Count(*) From t2 Where object_id In (Select object_id From t3); --强制使用循环排序算法 set timing on;   1.123 改变了执行计划
--WITH 简化复杂处理，效果比较好
With
    TEMP1 As (Select * From T),
    TEMP2 As (Select * From TT)
    Select TEMP1.*,TEMP2.* From TEMP1,TEMP2 Where TEMP1.Id = TEMP2.Id