CODEWITHSUNDEEP
sql-server
QVT
QVT

Reputation: 33

different result when use is not null

when I am using this query: 

SELECT * from  [dbo].[CRA] 
where [gender] like 'null'
      and [house] like 'null'
      and [residenttime] is  null
      and [worktime] is null
      and [loaiDN] like 'null'
      and [depend] like 'null'
      and [expGD] like 'null'
      and [Grincome] like 'null'
      and [dunocacTCTD] like 'null'
      and [tinhtrangno] like 'null'
      and [tgQHTD] like 'null'
      and [soduTB] like 'null'
      and [TlquaMB] like 'null'

result: 81 rows

but when using this query: 

SELECT * from  [dbo].[CRA] 
where [gender] not like 'null'
      and [house] not like 'null'
      and [residenttime] is not null
      and [worktime] is not null
      and [loaiDN] not like 'null'
      and [depend] not like 'null'
      and [expGD] not like 'null'
      and [Grincome] not like 'null'
      and [dunocacTCTD] not like 'null'
      and [tinhtrangno] not like 'null'
      and [tgQHTD] not like 'null'
      and [soduTB] not like 'null'
      and [TlquaMB] not like 'null'

The number of row removed is > 1000 rows

Why the two result are different?

Upvotes: 2

Views: 58

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

Here is another sample:

    SELECT * from  [dbo].[CRA] 
    where COALESCE( NULLIF([gender],'null')
                   ,NULLIF([house],'null')
                   ,[residenttime]
                   ,[worktime]
                   ,NULLIF([loaiDN],'null')
                   ,NULLIF([depend],'null')
                   ,NULLIF([expGD],'null')
                   ,NULLIF([Grincome],'null')
                   ,NULLIF([dunocacTCTD],'null')
                   ,NULLIF([tinhtrangno],'null')
                   ,NULLIF([tgQHTD],'null')
                   ,NULLIF([soduTB],'null')
                   ,NULLIF([TlquaMB],'null')
                   ) IS NULL --Will be returned only all the values is null or 'null'

BUT

           where COALESCE( NULLIF([gender],'null')
           ,NULLIF([house],'null')
           ,[residenttime]
           ,[worktime]
           ,NULLIF([loaiDN],'null')
           ,NULLIF([depend],'null')
           ,NULLIF([expGD],'null')
           ,NULLIF([Grincome],'null')
           ,NULLIF([dunocacTCTD],'null')
           ,NULLIF([tinhtrangno],'null')
           ,NULLIF([tgQHTD],'null')
           ,NULLIF([soduTB],'null')
           ,NULLIF([TlquaMB],'null')
           ) IS NOT NULL --It will be returned at least on value is not null('null')

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Since !(A and B) = !A or !B as per De Morgan's law.

Opposite will be 

SELECT * from  [dbo].[CRA] 
where [gender] not like 'null'
      or [house] not like 'null'
      or [residenttime] is not null
      or [worktime] is not null
      or [loaiDN] not like 'null'
      or [depend] not like 'null'
      or [expGD] not like 'null'
      or [Grincome] not like 'null'
      or [dunocacTCTD] not like 'null'
      or [tinhtrangno] not like 'null'
      or [tgQHTD] not like 'null'
      or [soduTB] not like 'null'
      or [TlquaMB] not like 'null'

or simply,

SELECT * from  [dbo].[CRA] 
where not ([gender] like 'null'
      and [house] like 'null'
      and [residenttime] is  null
      and [worktime] is null
      and [loaiDN] like 'null'
      and [depend] like 'null'
      and [expGD] like 'null'
      and [Grincome] like 'null'
      and [dunocacTCTD] like 'null'
      and [tinhtrangno] like 'null'
      and [tgQHTD] like 'null'
      and [soduTB] like 'null'
      and [TlquaMB] like 'null');

Upvotes: 2

Luke Shinn
Luke Shinn

Reputation: 346

I believe you are comparing to a string with your lines such as

and [house] not like 'null'

Instead leave the quotations off and use is null or not null

where Field is null
where Field is not null

Upvotes: 1

Related Questions