获取两个表不同的数据TableA
---------------------------------
ID TEXT
A test
B test1
C test2TableB
----------------------------------
ID KEY
-------------------------------------
SQL: select t.*
from
TableA t
inner join
TableB t1
on
t.ID <> t1.ID--------------------------------如果TableB中有数据,这条SQL就能查出不同ID的结果集。
但是,如果TableB表是空的话那么也就什么也查不出来,怎么解决?
---------------------------------
ID TEXT
A test
B test1
C test2TableB
----------------------------------
ID KEY
-------------------------------------
SQL: select t.*
from
TableA t
inner join
TableB t1
on
t.ID <> t1.ID--------------------------------如果TableB中有数据,这条SQL就能查出不同ID的结果集。
但是,如果TableB表是空的话那么也就什么也查不出来,怎么解决?
from
TableA t
left join
TableB t1
on
t.ID <> t1.ID
left join是可以取出TableA所有的数据,但是如果TableB有数据的时候,left join 是达不到取不同值的要求的。
select * from [TableA] a where not exists(
select 1 from [TableB] where ID=a.id
)
create table TableA(id varchar(10),text varchar(50))
go
insert into TableA
select 'A','test' union all
select 'B','test1' union all
select 'C','test2'
go
create table TableB(id varchar(10),[key] varchar(50))
insert into TableB
select 'A','test' union all
select 'D','test1' union all
select 'E','test2'select a.* from TableA a
left join TableB b on a.id=b.id
where a.id not in(select id from TableB)
--//结果
id text
---------- --------------------------------------------------
B test1
C test2(2 行受影响)
from
TableA t
left join
TableB t1
on
t.ID <> t1.ID
where t1.id is null
go
insert into TableA
select 'A','test' union all
select 'B','test1' union all
select 'C','test2'
go
create table TableB(id varchar(10),[key] varchar(50))
insert into TableB
select 'A','test' union all
select 'D','test1' union all
select 'E','test2'
select * from tablea t where not exists(select 1 from tableb where id=t.id)select a.* from TableA a left join TableB b on a.id=b.id where b.[key] is nulldrop table TableA,TableB /*id text
---------- --------------------------------------------------
B test1
C test2(2 行受影响)id text
---------- --------------------------------------------------
B test1
C test2(2 行受影响)*/