SQL/MySQL NOT NULL vs NOT EMPTY

Question

I'd like to limit my query to show only rows where a certain field is not empty. I found this thread where someone posed the same question and was told to use IS NOT NULL. I tried that, but I'm still getting rows where the field is empty.

What is the correct way to do this? Is Null the same thing as Empty in SQL/MySQL?

My query, if you're interested is: SELECT * FROM records WHERE (party_zip='49080' OR party_zip='49078' OR party_zip='49284' ) AND partyfn IS NOT NULL

Solution 1

When comparing a NULL value, the result in most cases becomes NULL and therefor haves the same result as 0 (the FALSE value in MySQL) inside WHERE and HAVING.

In your given example, you don't need to include IS NOT NULL. Instead simply use party_zip IN ('49080', '49078', '49284'). NULL can't be 49080, 49078, 49284 or any other number or string.

What you do need to think about though, is when checking for empty values. !party_zip won't return TRUE/1 if the value is NULL. Instead use OR columns IS NULL or !COALESCE(party_zip, 0)

Solution 2

I got it by using AND (partyfn IS NOT NULL AND partyfn != '')