Using SET Operators.

          1 The Union Operator
          ?The union operator returns results form both queries after eliminating duplications.\
          ?select employee_id,job_id
          ?from employees
          ?uniion
          ?select employ_id ,job_id
          ?from job_history;
          2 the all operator
          ?The union all opertor reutrn result from both queries,including all duplications
          3 interset
          ?select ster_id,qty from sales where qty>20;
          ?intersect
          ?select ster_id,qty from sales where ster_id like '7%';
          4 minus
          ?select ster_id,qty from sales where qty>20
          ?minus
          ?select ster_id from sales where ster_id like '7%'

          5 set operator guidelines
          ?. teh expressions in the select list must match in number and data type;
          ?. Parentheses can be used to alter the sequence of the execution
          ?.The order by clause:
          ??? can appear only at the very end of the statement
          ??? will accept the column name,aliases from thee firest select statement ,or thee positional notation
          ?.Duplicate row are atuomatically eliminated except in union all.
          ?.Column names from the first query appear in the result
          ?.The output is sorted in ascending order by default except in union all
          6 matching the select statement
          ?select department_id,to_number(null),location,hire_date
          ?from employees
          ?union
          ?select department_id,location_id,to_date(null)
          ?from departments;

          ?select employee_id,job_id,salary
          ?from employees
          ?union
          ?select employee_id,job_id,0
          ?from job_history;
          7 Controlling the order of the rows
          ?select 'sing' as "my dream" ,3,a_dummy
          ?from dual
          ?union
          ?select 'like''d like to teach',1
          ?from dual
          ?union
          ?select 'the world to',2
          ?from dual
          ?order by 2;

          posted on 2006-10-11 14:51 康文 閱讀(229) 評論(0)  編輯  收藏 所屬分類: 數據庫

          <2006年10月>
          24252627282930
          1234567
          891011121314
          15161718192021
          22232425262728
          2930311234

          導航

          統計

          常用鏈接

          留言簿(1)

          隨筆分類

          隨筆檔案

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 四平市| 犍为县| 台安县| 新余市| 寿光市| 东港市| 巴东县| 镇沅| 大悟县| 乌什县| 合作市| 同江市| 龙江县| 临朐县| 分宜县| 蒙阴县| 临澧县| 高州市| 景洪市| 中牟县| 筠连县| 蒙阴县| 全椒县| 宁蒗| 开封县| 新乐市| 马龙县| 德昌县| 莱西市| 大关县| 乐平市| 灵石县| 巫山县| 怀仁县| 聂拉木县| 合川市| 波密县| 定西市| 二连浩特市| 乌审旗| 南召县|