DB2 INTERSECT、EXCEPT、UNION集合操作测试

DB2 INTERSECT、EXCEPT、UNION集合操作测试

 

 

说明：DB2支持三种集合操作INTERSECT（交集）、EXCEPT（差集）、UNION（合集），在ORACLE等数据库里面也有类似的操作，但是细微的地方可能有些差别，现在专门测试一下，以求准确！玩数据千万要细心啊，否则会带来难以意料的结果，呵呵！

 

 

测试环境：

 

DROP TABLE  AA_WEEK;

CREATE TABLE AA_WEEK(

CODE SMALLINT NOT NULL,

EN_NAME VARCHAR(20) NOT NULL,

ABB VARCHAR(20),

CONSTRAINT P_AA_WEEK_CODE PRIMARY KEY (CODE)

);

COMMENT ON TABLE AA_WEEK IS

'星期';

COMMENT ON AA_WEEK

 (CODE IS '星期代码',

  EN_NAME IS '英文星期名',

  ABB IS '英文缩写'

 );

INSERT INTO AA_WEEK VALUES

(1,'Monday','Mon'),

(2,'Tuesday','Tues'),

(3,'Wednesday','Wed'),

(4,'Thursday','Thurs'),

(5,'Friday','Friday'),

(6,'Saturday','Sat'),

(7,'Sunday','Sun');

COMMIT;

 

查询一把，看看所有数据：

SELECT * FROM AA_WEEK;

+------+-----------+--------+

| CODE | EN_NAME   | ABB    |

+------+-----------+--------+

|    1 | Monday    | Mon    |

|    2 | Tuesday   | Tues   |

|    3 | Wednesday | Wed    |

|    4 | Thursday  | Thurs  |

|    5 | Friday    | Friday |

|    6 | Saturday  | Sat    |

|    7 | Sunday    | Sun    |

+------+-----------+--------+

 

一、交集运算：INTERSECT、INTERSECT ALL

 

-- 求交集：A交B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

INTERSECT                                       -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------
2       'Tuesday'       'Tues'
3       'Wednesday'     'Wed'

 

说明：INTERSECT和INTERSECT ALL是等效的。

 

二、差集运算：EXCEPT、EXCEPT ALL

 

-- 求差集：A减B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

EXCEPT                                          -- 集合B

SELECT * FROM AA_WEEK w WHERE w.CODE IN (2,3,4);        

 

说明：EXCEPT和EXCEPT ALL是等效的。

---------------------------------

1       'Monday'        'Mon'

 

三、合集运算：UNION、UNION ALL

 

-- 求合集：A+B（不消除重复行）

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

UNION ALL                                       -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------

2       'Tuesday'       'Tues'

3       'Wednesday'     'Wed'

4       'Thursday'      'Thurs'

1       'Monday'        'Mon'

2       'Tuesday'       'Tues'

3       'Wednesday'     'Wed'

 

-- 求合集：A+B（消除重复行）

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

UNION                                   -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------

1       'Monday'        'Mon'

4       'Thursday'      'Thurs'

2       'Tuesday'       'Tues'

3       'Wednesday'     'Wed'

 

说明：UNION和UNION ALL是不同的。UNION消除重复行记录，UNION ALL不消除重复行记录。