select a.* from a left join b on a.name=b.name where b.name is null
select * from ta a where not exists(select 1 from tb b where b.name=a.name )
select * from A where A.NAME not in(select Name from B)
select A.* from A left join B ON A.NAME=B.NAME WHERE B.info is nullselect * from A where not exists(select 1 from B where NAME=A.NAME)
建议到 MY SQL 版提问
select * from A where not exists(select * from B where name=A.name) 这样?
SET NOCOUNT ON if object_id('ta')is not null drop table ta go create table ta(ID int,NAME nvarchar(3),PASSWORD int) insert ta select 1,N'王',123 insert ta select 2,N'张',123 insert ta select 3,N'五',123 insert ta select 4,N'气',123 if object_id('tB')is not null drop table tB go CREATE TABLE TB(Name NVARCHAR(3),info INT) INSERT TB SELECT N'气',1 INSERT TB SELECT N'五',1 select * from ta where name not in(select name from tb ) /*ID NAME PASSWORD ----------- ---- ----------- 1 王 123 2 张 123*/ select * from ta a where not exists(select 1 from tb b where b.name=a.name ) /*ID NAME PASSWORD ----------- ---- ----------- 1 王 123 2 张 123*/
declare @a table(id int,a int,b int,c int) declare @b table(id int,a int,b int,c int) insert @a select null,2,3,4 union all select null,2,3,4 union all select null,2,3,4 union all select null,3,4,5 union all select null,3,4,5 insert @b select 1,2,3,4 union all select 2,3,4,5select * from @a select * from @bupdate @a set a.id=b.id from @a a,@b b where a.a=b.a and a.b=b.b and a.c=b.c select * from @a
贴错了declare @a table(id int,[name] varchar(50),password varchar(50)) declare @b table([name] varchar(50),info varchar(50)) insert @a select 1,'王',123 union all select 2,'张',123 union all select 3,'五',123 union all select 4,'气',123 insert @b select '气',1 union all select '五',1 select * from @a select * from @bselect * from @a a where not exists( select info from @b b where b.name=a.name)
where b.name is null
select A.* from A left join B ON A.NAME=B.NAME WHERE B.info is nullselect * from A where not exists(select 1 from B where NAME=A.NAME)
from A
where not exists(select * from B where name=A.name)
这样?
if object_id('ta')is not null drop table ta
go
create table ta(ID int,NAME nvarchar(3),PASSWORD int)
insert ta select 1,N'王',123
insert ta select 2,N'张',123
insert ta select 3,N'五',123
insert ta select 4,N'气',123
if object_id('tB')is not null drop table tB
go
CREATE TABLE TB(Name NVARCHAR(3),info INT)
INSERT TB SELECT N'气',1
INSERT TB SELECT N'五',1
select * from ta where name not in(select name from tb )
/*ID NAME PASSWORD
----------- ---- -----------
1 王 123
2 张 123*/
select * from ta a where not exists(select 1 from tb b where b.name=a.name )
/*ID NAME PASSWORD
----------- ---- -----------
1 王 123
2 张 123*/
declare @a table(id int,a int,b int,c int)
declare @b table(id int,a int,b int,c int)
insert @a select null,2,3,4
union all select null,2,3,4
union all select null,2,3,4
union all select null,3,4,5
union all select null,3,4,5
insert @b select 1,2,3,4
union all select 2,3,4,5select * from @a
select * from @bupdate @a set a.id=b.id from @a a,@b b where a.a=b.a and a.b=b.b and a.c=b.c select * from @a
declare @b table([name] varchar(50),info varchar(50))
insert @a select 1,'王',123
union all select 2,'张',123
union all select 3,'五',123
union all select 4,'气',123
insert @b select '气',1
union all select '五',1
select * from @a
select * from @bselect * from @a a where not exists(
select info from @b b where b.name=a.name)