CODEWITHSUNDEEP
phpmysqldatabasenull
user1250279
user1250279

Reputation:

MySql - Incorrect query results while checking for null values

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?

Upvotes: 1

Views: 572

Answers (3)

Randy
Randy

Reputation: 16673

null does not equal null.

you need to specify IS NULL

Upvotes: 1

user319198
user319198

Reputation:

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

Upvotes: 0

deed02392
deed02392

Reputation: 5032

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.

Upvotes: 4

Related Questions