判断某表中值在另一个表中不存在的查询条件怎么写?
如表A
id name
1 n1
2 n2
3 n3
4 n4
表B
id
1
3select * from A,B where (A.id在B.id中不存在)括号里的条件怎么写?40分献上
如表A
id name
1 n1
2 n2
3 n3
4 n4
表B
id
1
3select * from A,B where (A.id在B.id中不存在)括号里的条件怎么写?40分献上
select * from A where id not in(select id from B)
*
from
a
where
id
not in
(select id from b)
A
id name
1 n1
2 n2
3 n3
4 n4
表B
id score
1 s1
3 s2我想查出的结果是
id name score
1 n1 s1
2 n2 none
3 n3 s3
4 n4 none
怎么写查询?
求大侠赐教
insert into #a
select 1,'n1' union all
select 2,'n2' union all
select 3,'n3' union all
select 4,'n4'
create table #b(id int)
insert into #b
select 1 union all
select 3
select * from #a where not exists(select 1 from #b where #a.id=#b.id)
a.*,isnull(b.score,'none') as score
from
a
left join
b
on
a.id=b.id
where id not in(select id from B)
insert into #a
select 1,'n1' union all
select 2,'n2' union all
select 3,'n3' union all
select 4,'n4'
create table #b(id int,score varchar(10))
insert into #b
select 1,'s1' union all
select 3,'s2'
select #a.*,#b.score from #a left join #b on #a.id=#b.id