Post reply

Query removing NULLs when not chosen to

  • November 23, 2021 at 5:39 pm

    Go to Answer

    #3956174

    I have a result set below from this query:

    SELECT [CALL_ID], [DATE], [TIME], [CAMPAIGN], [CALL_TYPE], [DISPOSITION], [HANDLE_TIME]
    FROM A2WH..CallLogCommon
    WHERE [DATE] = '2021-11-05' AND [CAMPAIGN] = 'Chareleston IB'
    AND [CALL_TYPE] NOT LIKE '3rd%'
    ORDER BY [DATE]

    If I change my query to this to get rid of the word 'Test' in my Disposition column my NULLs go away too which seems like it shouldn't happen and is not desired... any ideas?:

    SELECT [CALL_ID], [DATE], [TIME], [CAMPAIGN], [CALL_TYPE], [DISPOSITION], [HANDLE_TIME]
    FROM A2WH..CallLogCommon
    WHERE [DATE] = '2021-11-05' AND [CAMPAIGN] = 'Chareleston IB'
    AND [CALL_TYPE] NOT LIKE '3rd%' AND [DISPOSITION] <> 'Test'
    ORDER BY [DATE]

  • November 23, 2021 at 5:56 pm

    Answer

    #3956175

    Any time you do a comparison such as =, <>, or LIKE in a where clause (or inner join), you are implicitly excluding rows where that column is null, since null is never equal to, not equal to, nor like any value. Null doesn't even equal null -- that's why we use IS NULL or IS NOT NULL rather than "= NULL" or "<> NULL".

    So if you need to include the nulls, you might want to use something like

    AND ([DISPOSITION] <> 'Test' OR [DISPOSITION] IS NULL)

  • November 23, 2021 at 5:59 pm

    #3956176

    That works and makes sense! Thanks!

  • November 27, 2021 at 6:06 am

    This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply