CODEWITHSUNDEEP
sqlsql-server
Sampath
Sampath

Reputation: 65940

`0` values are not shown when I put a string value filter SQL

SELECT DISTINCT ISNULL(a.[BPOAGE], 0) AS BPOAGE, a.[BPOAttic]
FROM [Legacy].[dbo].[MyTables] as a

Result :

enter image description here

SELECT DISTINCT ISNULL(a.[BPOAGE], 0) AS BPOAGE, a.[BPOAttic]
FROM [Legacy].[dbo].[MyTables] as a
where (a.[BPOAGE] not in ('New'))

Result :

enter image description here

Q : Can you tell me why 0 values are not shown when I put this condition a.[BPOAGE] not in ('New')?

Upvotes: 1

Views: 51

Answers (1)

Mani
Mani

Reputation: 344

Sql works on Three valued logic.It considers NULL as unknown,since it is unknown it will not got selected in the condition you write.If you want to include null rewrite the code as

SELECT DISTINCT ISNULL(a.[BPOAGE], 0) AS BPOAGE,a.[BPOAttic] FROM [Legacy].
    [dbo].[MyTables] as a where (a.[BPOAGE] not in ('New') or a.[BPOAGE] is null)

Upvotes: 2

Related Questions