CODEWITHSUNDEEP
mysqlsqlnull
cream
cream

Reputation: 1129

SQL/MySQL NOT NULL vs NOT EMPTY

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

Upvotes: 17

Views: 115570

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

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)

Upvotes: 12

cream
cream

Reputation: 1129

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

Upvotes: 40

Related Questions