關於SQL中的Except

標籤：

 1 use test1 2 select address_district,COUNT(address_district) as total from hr_personal_test group by address_district with rollup; 3 create table test1 (id int); 4 create table test2 (num int); 5  6 --1 1(1,2,3) 2(1,4) 7 --insert into test1 select 1 union select 2 union select 3 8 --insert into test2 select 1 union select 4 9 10 --select id from test1 except select num from test2;-------返回結果2，311 12 13 delete from test1 ;14 delete from test2;15 --2 1(1,2) 2(1,2,3,4)16 --insert into test1 select 1 union select 2 17 --insert into test2 select 1 union select 2 union select 3 union select 418 --select id from test1 except select num from test2; --------返回結果 沒有19 20 delete from test1 ;21 delete from test2;22 --3 1(1,2) 2(3,4) 23 insert into test1 select 1 union select 2 24 insert into test2 select 3 union select 425 select id from test1 except select num from test2-------- 返回結果 1,226 27 delete from test1 ;28 delete from test2;29 --4 1(1,2,3) 2(1,2)30 insert into test1 select 1 union select 2 union select 331 insert into test2 select 1 union select 2 32 select id from test1 except select num from test2--------返回結果3

 

create table test1(id int ,name nvarchar(20))create table test2(id int ,post nvarchar(4))insert into test1 (name) values (‘a‘),(‘b‘),(‘b‘),(‘c‘)insert into test2 (post) values(‘a‘)select name from test1 except select post from test2--------返回 b,cselect name from test1 where name not in(select post from test2)---------返回 b,b,c

 

由此可以得出以下幾點：

1、Except運算子對比的是資料，而非按照不同表中相同欄位名的欄位。

2、Except運算子簡而言之：尋找左邊表中不在右邊表中存在的資料。用集合來表示應該是 A-A交B 而非 （A並B）-（A交B）。

3、Except運算子會去重，而not in 不會。

關於SQL中的Except