Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id having min(salary)>(select mn(salary) from employees where department_id = 50)
* min salary in department_id=50
* list department_id, min(salary) group by department_id and min(salary)> (min salary in departmentid=50).
2. select department_id, min(salary) from employees group by department_id having min(salary)>(select min(salary) from employees group by department_id);
* this sentense is wrong. has the subquery operates multi results.
Multi-row subqueires: any, all, in
IN: equals to any member in the list
any: 1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to each value in a list or returned by a query. 3) evaluates to FALSE if the query returns no rows.
all: 1) must be preceded by =, !=, >, <, <=, >=. 2) compares a value to every value in a list or returned by a query. 3) evaluates to TRUE if the query returns no rows.
e.g.:
ndocsortid: 1...9
select ndocsortid from wf_docsort where nmoduleinfoid=2: results: 4,5,9
select ndocsortid from wf_docsort where nmoduleinfoid=9: results: null;
1. select distinct ndocsortid
from wf_procname
where ndocsortid < all (select ndocsortid from wf_docsort where nmoduleinfoid=2)
order by ndocsortid
results: 1,2,3
2. select nmoduleinfoid, ndocsortid, nprocid
from wf_procname
where ndocsortid < any (select ndocsortid from wf_docsort where nmoduleinfoid=2)
order by ndocsortid
results: 1,2,3,4,5,6,7,8
3. select distinct ndocsortid
from wf_procname
where ndocsortid < all (select ndocsortid from wf_docsort where nmoduleinfoid=9)
order by ndocsortid
results: 1,2,3,4,5,6,7,8,9
4. select&
