SQL query: not working properly for blank values

Question

I have an issue, where SQL queries do not work correctly for blank values.

A very brief summary:

• I have TABLE A with 200k rows (no duplicates). In this table there is field called MailingCountry. Sometimes this field is blank.
• I have TABLE B with 50k rows (no duplicates), which is a subset of TABLE A built using a query
• I have TABLE C with 10k rows (no duplicates), which is a subset of TABLE A buolt using a filter where MailingCountry is not equal 'NZ' or 'New Zealand'

I should get the same results if I do either of the following:

1. Run the below query:

SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
WHERE b.SubscriberKey IS NULL
AND a.MailingCountry  <> 'NZ' 
AND a.MailingCountry <> 'New Zealand'

2. Run the below query:

SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
LEFT JOIN [TABLE C] c
ON a.ContactKey = c.ContactKey
WHERE b.SubscriberKey IS NULL
AND c.ContactKey IS NULL

However, I get completely different results. Doing the maths manually it is clear that query 1 is also removing rows where MailingCountry = '' (ie has a blank value)

Is this a known issue? Or a bug? Or have I stuffed something up?

Thanks

Answer 1

The issue you are facing is that SQL uses Three-Valued Logic, which means there is True, False and Unknown(NULL). A value that is NULL therefore cannot be true or false, which causes issues with Not Equals conditional statements in SQL.

For instance, lets say TableA has values in ColumnA of 1|2|NULL and you use the following SQL:

SELECT *
FROM TableA
WHERE ColumnA <> 1

This would only return the 2nd row with a value of 2.

This is because anything compared to a null value will result in null. And any WHERE statement will need a returned value of true from the conditional logic in order to be included in the results.

So if you want to include nulls inside your comparison, you would need to have them specified in your Query.

There are two ways you could do this:

SELECT *
FROM TableA
WHERE ColumnA <> 1
OR ColumnA IS NULL

This will then include NULL values inside of your returned data.

Another option you can use to reduce the number of conditional statements inside of your query is to use the ISNULL() SQL function:

SELECT *
FROM TableA
WHERE ISNULL(ColumnA, 2) <> 1

This will then take any null value inside of ColumnA and assign it a value of 2 before doing the comparison logic. This will then make all null values return true from that condition. This can also be accomplished with COALESCE().

Answer 2

In your second query, write C.ContactKey is not NULL. Something like

SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
LEFT JOIN [TABLE C] c
ON a.ContactKey = c.ContactKey
WHERE b.SubscriberKey IS NULL
AND c.ContactKey IS not NULL

Also for consistent results, ensure that you filter out NL records in your query to populate Table B records.

Answer 3

@Gortonington's answer regarding Three-Valued Logic solved the problem. Putting some additional info in an answer here for the sake of formatting.

Specifically in this case I used a CASE statement that looked like this:

SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
WHERE a.Seasonal_Gift_Inspiration = 1
AND b.SubscriberKey IS NULL
AND (case 
  when a.MailingCountry = 'New Zealand' then 1
  when a.MailingCountry = 'NZ' then 1
  when a.MailingCountry = '' then 2
  when a.MailingCountry IS NULL then 2
  else 2 end) <> 1