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

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