SQL集合运算：差集、交集、并集

1、差集( except )

select a from t_a

except

select a from t_b

 

-- 也可写作：

select a from t_a where a not in (select a from t_b)

 

-- 多个字段时：

select a,b from t_a

except

select a,b from t_b

 

-- 多字段的查集也可写成：

select a,b from t_a where (a,b) not in (select a,b from t_b)

 

2、交集( intersect )

select a from t_a

intersect

select a from t_b

 

-- 也可写作:

   select a from t_a where a in (select a from t_b)

 

3、并集( union )

select a from t_a

union distinct

select a from t_b