1

My query is not giving proper results when I try to select user records that don't have email:

select * from users where email=''

And:

select * from users where email=NULL

Both give different results but neither give correct results. How can I get correct results?

Improve this question

3 Answers 3

Reset to default
4

It's IS NULL or IS NOT NULL.

select * from users where email IS NULL

Depending on HOW a user can have 'no e-mail', you should set the default value of the field to NULL and make the field nullable. Then if an insert is performed of a new record in that table, the e-mail will be NULL rather than empty.

Alternatively if you're already stuck with a mismatched table, try something like:

select * from users where LENGTH(COALESCE(email,'')) = 0

That'll give you all records with an 'empty' value for email, treating NULL as empty.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Exactly. For some reason in MySQL, NULL is not equal to NULL. I've seen in my company codebase tons of queries in the form WHERE something IS NULL OR something = '' because nobody remembers what is having "no value"
1

null does not equal null.

you need to specify IS NULL

Improve this answer

Comments

0

Mysql treats both of them different

email='' checks for blank values and email=NULL checks for null values that is why both of them giving different results.

You should use is NULL it checks both blank and null values.

SELECT *  FROM users WHERE email IS NULL
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.