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)