用not exists反真 select * from ho_rooms a where not exists(select 1 from ho_guest where room=a.room )
举个例子: declare @T table(Col int) insert @T select 1 insert @T select 2 declare @T2 table(Col int)insert @T2 select 1 insert @T2 select nullselect * from @T where col not in(select col from @T2)--没记录select * from @T where Col not in(select isnull(col,'') from @T2)--有记录select * from @T t where not exists(select 1 from @t2 where col=t.col)--有记录
SQL-92 标准要求对空值的等于 (=) 或不等于 (<>) 比较取值为 FALSE 所有涉及null的比较都返回false可在语句前加 set ansi_nulls off
select * from ho_rooms a where not exists(select 1 from ho_guest where room=a.room )
declare @T table(Col int)
insert @T select 1
insert @T select 2
declare @T2 table(Col int)insert @T2 select 1
insert @T2 select nullselect * from @T where col not in(select col from @T2)--没记录select * from @T where Col not in(select isnull(col,'') from @T2)--有记录select * from @T t where not exists(select 1 from @t2 where col=t.col)--有记录
所有涉及null的比较都返回false可在语句前加
set ansi_nulls off