NULL == 0 in MySql?

Question

I use query like this:
SELECT table.field FROM table WHERE table.field > 0
Would WHERE clause be working if table.field contain NULL value?

What do you want to happen if field is null? Include in results or not? — Bohemian
– Bohemian ♦, Commented Mar 22, 2014 at 12:01
your query already does that: A null value is not true for any comparison other than by is null and is not null — Bohemian
– Bohemian ♦, Commented Mar 22, 2014 at 12:05

Gordon Linoff · Accepted Answer · 2014-03-22 12:01:30Z

2

The where clause will work the way it is supposed to. Just about all comparisons to NULL return NULL, which is treated as "false" in the where clause.

So, all these expressions will return no rows if the value is NULL:

WHERE table.field > 0
WHERE table.field = 0
WHERE table.field < 0
WHERE table.field <> 0

If you are concerned about NULL values, test for them explicitly (using is null or is not null) or use a function like coalesce() to give the field a value when it is NULL.

answered Mar 22, 2014 at 12:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Grijesh Chauhan Over a year ago

Do we have some way to CAST NULL into boolean in MySQL? I tried but couldn't find.

Gordon Linoff Over a year ago

@GrijeshChauhan . . . You don't need to cast it. Just say field is not null and MySQL gives a boolean result, which is "true" (1) if the field is not null and "false" (0) if the field is.

Grijesh Chauhan Over a year ago

Ok, I got it, I tried > SET @a = NULL; SELECT @a is NULL; thanks!

abdulmanov.ilmir Over a year ago

Thanks for this complete answer!

echo_Me · Accepted Answer · 2014-03-22 12:04:16Z

0

you can do this to be sure.

 SELECT table.field FROM table WHERE table.field > 0
                               AND table.field is not null

edited Mar 22, 2014 at 12:04

answered Mar 22, 2014 at 11:57

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

2 Comments

Bohemian Over a year ago

-1 the test for is not null adds no value, since the original test for > 0 will not be true if field is null.

Bohemian Over a year ago

but now it will return every non-null row, eg if field is -1 it will be returned: That hardly makes sense if the original test was for field > 0. You are barking up the wrong tree.

Collectives™ on Stack Overflow

NULL == 0 in MySql?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related