1 Guidelines for Using Subqueries
a Enclose subqueries in parenttheses
b placce subqueries on the right side of the comparision condition
c the order by clause in the subquery is not needed
d using single-row operators with single-row subqueries and use multiple -row operator with multiple-row subqueries .
single-row subqueries can work as a expression,and muitiple-row subqueries can only be used with in all any ,i will talk it later
select last_name where job_idd=(select job_id
from employees
where imployee_id=141)
2 The HAVING CLause with Subqueries
a The Oracle server execute subqueries first
b The Oracle return result into the HAVING clause of the main query
select department_id,min(salary)
from employee
group by department_id
having min(salary)>
(select min(salary)
from employees
where department_id=50);
3 Multiple-Row Subqueries
a Return more than one row
Using mutiple-row comparsion operator
select employee_id
from employees
where salary<any
(select salary
from employees
where job_id='ddd')
select employee_id
from employees
where salary<all
(select salary
from employees
where job_id='ddd')
select emp.last_name
from employees emp
where emp.employee_id not in
(select mgr.manager_id
from employees mgr)