Sampath Sampath - 5 months ago 11
SQL Question

`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')
?

Answer

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)