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'
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不消除重复行记录。
转自:http://lavasoft.blog.51cto.com/62575/37650/