0

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'
  1. 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

4
  • You would need to add in OR a.MailingCountry IS NULL to your query as Null values do not show as true or false but undefined, so will be removed in filters like that. Dec 9, 2019 at 2:49
  • @Gortonington are you referring to the first or second query? The second query gives the correct result. The first query gives the wrong result and is removing rows where MailingCountry is blank even though I explicitly only say to remove rows where the string is 'NZ' or 'New Zealand'
    – Ben
    Dec 9, 2019 at 22:36
  • The first one. The null is not being passed in because it can be neither true nor false in a comparison to a value. I will try to write up an answer with more info later for better explanation Dec 10, 2019 at 0:24
  • 1
    I have now added more details inside an answer. Dec 10, 2019 at 14:16

3 Answers 3

3

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().

1
  • what you explained about the Three-Valued Logic has fixed the problem. I ended up having to create a CASE statement to get it to work with multiple conditions (I will put it in an answer below since it won't format correctly here). Thanks
    – Ben
    Dec 11, 2019 at 23:00
0

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.

0

@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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.