关于SQL中的Except
1use test1
2select address_district,COUNT(address_district) as total from hr_personal_test group by address_district with rollup;
sql中delete用法3create table test1 (id int);
4create 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，3
11
12
13delete from test1 ;
14delete 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 4
18--select id from test1 except select num from test2; --------返回结果没有
19
20delete from test1 ;
21delete from test2;
22--3 1(1,2) 2(3,4)
23insert into test1 select1union select2
24insert into test2 select3union select4
25select id from test1 except select num from test2-------- 返回结果 1,2
26
27delete from test1 ;
28delete from test2;
29--4 1(1,2,3) 2(1,2)
30insert into test1 select1union select2union select3
31insert into test2 select1union select2
32select 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,c
select 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 不会。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。