CODEWITHSUNDEEP
mysqlselectnull
stackoverflow
stackoverflow

Reputation: 19504

In MySQL why isn't my NULL value being compared to correctly?

I have the following table

Select * from Peeps
Id Name Age 
1  Sam  16
2  John NULL


Select 
Name, 
Age,
(If Age=NULL,1,0) AS Z
from peeps;

Results:

+------+------+---+
| name | age  | Z |
+------+------+---+
| Sam  |   16 | 0 |
| John | NULL | 0 |
+------+------+---+

Desired Results

+------+------+---+
| name | age  | Z |
+------+------+---+
| Sam  |   16 | 0 |
| John | NULL | 1 |
+------+------+---+

Upvotes: 2

Views: 79

Answers (2)

Matt Gibson
Matt Gibson

Reputation: 14959

Use this instead:

IF Age IS NULL

Using NULL will actually represent the null value, whereas "NULL" will act as a string with the letters N-U-L-L in it. Not the same at all.

Final query:

SELECT Name, 
       Age,
       (IF Age IS NULL, 1, 0) AS Z
  FROM peeps

Upvotes: 3

xofer
xofer

Reputation: 1030

Try:

Select 
Name, 
Age,
(If Age IS NULL,1,0) AS Z
from peeps;

http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html

Upvotes: 4

Related Questions