Best way to handle NULL in MySQL logical subexpressions

Question

I am not used to working with NULL and i am facing some problems. For example one would think that this query returns all customers:

SELECT * from customers WHERE ( job LIKE '%expert%' OR job NOT LIKE '%expert%' )

But reality is that it won't return customers with a NULL job.

That's an example. A more real world query could be:

SELECT * from customers WHERE NOT ( job LIKE '%expert%' )

And to work properly it could be rewritten like:

SELECT * from customers WHERE NOT ( job IS NOT NULL AND job LIKE '%expert%' )

What's the best way of dealing with this NULL in logical subexpressions? I am mainly a PHP programmer for small scripts (i'm not used to ORM or other frameworks).

Check [this question](http://stackoverflow.com/questions/3924400/like-does-not-accept-null-value) out — D4V1D, Mar 31 '15 at 07:04
do you **want** to return rows with a `null` job or do your **not** want to return them? — Simulant, Mar 31 '15 at 08:22
Yes, I want to return all the non-experts, even if they are null. — LatinSuD, Mar 31 '15 at 09:38

score 1 · Answer 1 · answered Jan 07 '16 at 09:47

1

You can get the proper results by using this query

SELECT * from customers WHERE NOT ( job LIKE '%expert%' ) OR job IS NULL

answered Jan 07 '16 at 09:47

vikas pal

26
4

score 0 · Answer 2 · answered Jan 10 '16 at 19:25

0

I guess COALESCE() is quite handy in most cases.

My last example could be like:

SELECT * from customers WHERE NOT ( COALESCE(job,'') LIKE '%expert%' )

It replaces job with an empty string if it happened to be NULL.

answered Jan 10 '16 at 19:25

LatinSuD

1,779
12
19

Best way to handle NULL in MySQL logical subexpressions

2 Answers2