subqueries

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)

posted on 2006-09-22 16:25 康文 阅读(249) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜