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)

          隨筆分類

          隨筆檔案

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 综艺| 泰顺县| 大庆市| 西丰县| 西和县| 年辖:市辖区| 渭南市| 米泉市| 石河子市| 鄂州市| 灵台县| 西乌珠穆沁旗| 得荣县| 江孜县| 高平市| 朝阳区| 荣成市| 清流县| 方山县| 商南县| 巴彦县| 锡林浩特市| 宜昌市| 太湖县| 舞钢市| 公主岭市| 香河县| 东方市| 大石桥市| 文登市| 新乡县| 九龙县| 偏关县| 康定县| 屯门区| 清河县| 大余县| 宜宾市| 名山县| 阜新| 武乡县|