I have a column that is still showing null even when I check to see if it is null

Question

I have this SQL Query below:

SELECT c.COURT_ID, ci.BODY_TEXT
,  TYPE = (
         SELECT 
         CASE WHEN DESCRIPTION = 'Courts of Appeals' THEN 'appellate court'
         WHEN DESCRIPTION = 'District Courts' THEN 'special trial court'
         WHEN DESCRIPTION IS NULL OR c.FINE_ID IS NULL THEN 'legislative court'
         ELSE DESCRIPTION
         END
         FROM [STAGING].COURT_LOCATION
         WHERE c.FINE_ID = FINE_ID)   
FROM [STAGING].[COURT] c
LEFT OUTER JOIN [STAGING].COURT_INFO ci on c.COURT_ID = ci.COURT_ID     

Sometimes TYPE column will show as NULL.

When it is NULL, the c.FINE_ID column is also NULL.

So when TYPE is NULL, I want it to default to legislative court.

So I made a CASE statement that checks both DESCRIPTION from the [STAGING].COURT_LOCATION table and c.FINE_ID from the [COURT] table.

I don't get any errors, but it still shows NULL for TYPE when FINE_ID is NULL.

Is there any way to get this to work?

Thanks!

Answer 1

Your basic problem null doesn't equal null, so WHERE c.FINE_ID = FINE_ID is not true when FINE_ID is null.

The simplest fix is to use COALESCE:

SELECT c.COURT_ID, ci.BODY_TEXT
,  TYPE = COALESCE(
         (SELECT 
         CASE WHEN DESCRIPTION = 'Courts of Appeals' THEN 'appellate court'
         WHEN DESCRIPTION = 'District Courts' THEN 'special trial court'
         WHEN DESCRIPTION IS NULL OR c.FINE_ID IS NULL THEN 'legislative court'
         ELSE DESCRIPTION
         END
         FROM [STAGING].COURT_LOCATION
         WHERE c.FINE_ID = FINE_ID), 'legislative court') 
FROM [STAGING].[COURT] c
LEFT OUTER JOIN [STAGING].COURT_INFO ci on c.COURT_ID = ci.COURT_ID