DB2 EXCEPT INTERSECT UNION UNION ALL

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不消除重复行记录。