select * from b where b.name1 not in (select name from a)
select name1 from b表 not exists( select name from a表 )
CREATE TABLE A (NAME varchar(10)) INSERT INTO A SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'CREATE TABLE B (NAME1 varchar(10)) INSERT INTO B SELECT 'aa' UNION ALL SELECT 'a' UNION ALL SELECT 'a1' UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL SELECT 'c1' select name1 from b except select name from a /* name1 ---------- a1 aa c1(3 行受影响)*/
select name1 from b where not exists(select 1 from a)select name1 from b where name1 not in (select name from a )select name1 from b except select name from a
LZ要查两张中不同的,而不是其中一个表的不同select name from a except select name1 from b union select name1 from b except select name from a
select distinct a.search from a full join b on checksum(a.search)=checksum(b.Name) where a.id is null or b.id is NULL
集合操作最快,我觉得except足够快了。
select distinct a.search from a full join b on checksum(a.search)=checksum(b.Name) where a.id is null or b.id is NULL 性能问题有: 1、distinct ,需要合并重复,隐式做了order by。 2、full join,多出很多null值的数据。 3、checksum放在=号左边,也是一个大忌。 4、where中的or
--One select name1 from b left outer join a on a.name<>b.name1--Two select name1 from b where name1 NOT IN (select name from a)
我觉得except 足够好了,你还有什么不满意的吗?
not in 、not exists这些都需要进行关联、行匹配。但是except/union 这类型的是集合操作
我按这个输了一遍但是结果不对不知道为什么? if OBJECT_ID('a') is not null drop table a if OBJECT_ID('b') is not null drop table b go create table a(name varchar(4)) create table b(name varchar(4)) insert into a select 'a' union all select 'b' union all select 'c' union all select 'd' insert into b select 'a' union all select 'b' union all select 'aa' union all select 'bb' union all select 'cc' union all select 'c' select * from a except select * from b union all select * from b except select * from a
SELECT * FROM ( select name1 from b except select name from a) a UNION ALL SELECT * FROM ( select name from a except select name1 from b) b要加别名
select name from a where not exist(select * from b where name1=a.name) union select name1 from b where not exist (select * from a where name=b.name1)
我上周五去面试宁婆畅想有限公司广州分公司软件实施也考到这里. 两个表分别是table1,table2; 要查出name不同的记录;可以说table table2结构完成相同的.只是不知哪个表的记录多; 发现自已真的不会sql server
INSERT INTO A
SELECT 'a'
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'c'CREATE TABLE B (NAME1 varchar(10))
INSERT INTO B
SELECT 'aa'
UNION ALL
SELECT 'a'
UNION ALL
SELECT 'a1'
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'c'
UNION ALL
SELECT 'c1'
select name1 from b except select name from a
/*
name1
----------
a1
aa
c1(3 行受影响)*/
union
select name1 from b except select name from a
select distinct a.search from a full join b
on checksum(a.search)=checksum(b.Name)
where a.id is null or b.id is NULL
on checksum(a.search)=checksum(b.Name)
where a.id is null or b.id is NULL
性能问题有:
1、distinct ,需要合并重复,隐式做了order by。
2、full join,多出很多null值的数据。
3、checksum放在=号左边,也是一个大忌。
4、where中的or
select name1 from b left outer join a on a.name<>b.name1--Two
select name1 from b where name1 NOT IN (select name from a)
if OBJECT_ID('a') is not null
drop table a
if OBJECT_ID('b') is not null
drop table b
go
create table a(name varchar(4))
create table b(name varchar(4))
insert into a
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'
insert into b
select 'a' union all
select 'b' union all
select 'aa' union all
select 'bb' union all
select 'cc' union all
select 'c'
select * from a except select * from b
union all
select * from b except select * from a
select name1 from b
except
select name from a) a
UNION ALL
SELECT * FROM (
select name from a
except
select name1 from b) b要加别名
select name from a where not exist(select * from b where name1=a.name)
union
select name1 from b where not exist (select * from a where name=b.name1)
两个表分别是table1,table2;
要查出name不同的记录;可以说table table2结构完成相同的.只是不知哪个表的记录多;
发现自已真的不会sql server