select * from a where not exists(select 1 from b where a.a1=b.b1) union select * from b where not exists(select 1 from a where a.a1=b.b1)
--------------------------------------------------- create table a( id char(2) ) create table b( id char(2) ) insert into a values('a') insert into a values('b') insert into a values('c') insert into a values('d') insert into a values('e') insert into a values('f') ------------------------- insert into b values('a') insert into b values('c') insert into b values('e') insert into b values('s') insert into b values('m') select c.id from a cross apply(select *from b where a.id=b.id)c /* id a c e */
select a1 from a where a1 not in (select b1 from b) union all select b1 from b where b1 not in (select a1 from a) select a1 from a where not exists (select 1 from b where b.b1 = a.a1) union all select b1 from b where not exists (select 1 from a where a.a1 = b.b1)
create table A(a1 varchar(10)) insert into a values('a') insert into a values('b') insert into a values('c') insert into a values('d') insert into a values('e') insert into a values('f') insert into a values('g') create table b(b1 varchar(10)) insert into b values('a') insert into b values('c') insert into b values('f') insert into b values('g') go--1.如果仅仅是查A表不在B表的数据,则如下: select a1 from a where a1 not in (select b1 from b) /* a1 ---------- b d e(所影响的行数为 3 行) */select a1 from a where not exists (select 1 from b where b.b1 = a.a1) /* a1 ---------- b d e(所影响的行数为 3 行) */--2.如果是查两表相互不存在则如下: select a1 from a where a1 not in (select b1 from b) union all select b1 from b where b1 not in (select a1 from a) /* a1 ---------- b d e(所影响的行数为 3 行) */ select a1 from a where not exists (select 1 from b where b.b1 = a.a1) union all select b1 from b where not exists (select 1 from a where a.a1 = b.b1) /* a1 ---------- b d e(所影响的行数为 3 行) */select a1 , 'A' [table] from a where a1 not in (select b1 from b) union all select b1 , 'B' [table] from b where b1 not in (select a1 from a) /* a1 table ---------- ----- b A d A e A(所影响的行数为 3 行) */select a1 , 'A' [table] from a where not exists (select 1 from b where b.b1 = a.a1) union all select b1 , 'B' [table] from b where not exists (select 1 from a where a.a1 = b.b1) /* a1 table ---------- ----- b A d A e A(所影响的行数为 3 行) */drop table a , b
union
select * from b where not exists(select 1 from a where a.a1=b.b1)
create table a(
id char(2)
)
create table b(
id char(2)
)
insert into a values('a')
insert into a values('b')
insert into a values('c')
insert into a values('d')
insert into a values('e')
insert into a values('f')
-------------------------
insert into b values('a')
insert into b values('c')
insert into b values('e')
insert into b values('s')
insert into b values('m')
select c.id from a cross apply(select *from b where a.id=b.id)c
/*
id
a
c
e
*/
union all
select b1 from b where b1 not in (select a1 from a)
select a1 from a where not exists (select 1 from b where b.b1 = a.a1)
union all
select b1 from b where not exists (select 1 from a where a.a1 = b.b1)
insert into a values('a')
insert into a values('b')
insert into a values('c')
insert into a values('d')
insert into a values('e')
insert into a values('f')
insert into a values('g')
create table b(b1 varchar(10))
insert into b values('a')
insert into b values('c')
insert into b values('f')
insert into b values('g')
go--1.如果仅仅是查A表不在B表的数据,则如下:
select a1 from a where a1 not in (select b1 from b)
/*
a1
----------
b
d
e(所影响的行数为 3 行)
*/select a1 from a where not exists (select 1 from b where b.b1 = a.a1)
/*
a1
----------
b
d
e(所影响的行数为 3 行)
*/--2.如果是查两表相互不存在则如下:
select a1 from a where a1 not in (select b1 from b)
union all
select b1 from b where b1 not in (select a1 from a)
/*
a1
----------
b
d
e(所影响的行数为 3 行)
*/
select a1 from a where not exists (select 1 from b where b.b1 = a.a1)
union all
select b1 from b where not exists (select 1 from a where a.a1 = b.b1)
/*
a1
----------
b
d
e(所影响的行数为 3 行)
*/select a1 , 'A' [table] from a where a1 not in (select b1 from b)
union all
select b1 , 'B' [table] from b where b1 not in (select a1 from a)
/*
a1 table
---------- -----
b A
d A
e A(所影响的行数为 3 行)
*/select a1 , 'A' [table] from a where not exists (select 1 from b where b.b1 = a.a1)
union all
select b1 , 'B' [table] from b where not exists (select 1 from a where a.a1 = b.b1)
/*
a1 table
---------- -----
b A
d A
e A(所影响的行数为 3 行)
*/drop table a , b
“使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。 ”
select a1 from a where not exists(select 1 from b where a.a1=b.b1)
union
select b1 from b where not exists(select 1 from a where a.a1=b.b1)
SELECT A1 FROM
(
SELECT * FROM A
UNION ALL
SELECT * FROM B
) a GROUP BY a1 HAVING COUNT(a1)=1