0

Possible Duplicate:
Why does column = NULL return no rows?

I am trying to query a row from mysql database where value of certain column is null, here is my query:

SELECT * FROM `mytable` WHERE mycolumn=null

Actually I am using Boolean column as a three way switch where 0,1 and null meant to be used for different results.

Above query is returning a zero result set and if I remove where mycolumn=null thing, its returning right result with some rows having null value for the specified column.

Improve this question
2
  • Does SELECT * FROM mytable WHERE mycolumn IS null help? Also note that you don't need to use the ` to delineate table names if there are no spaces in the name Commented Sep 16, 2012 at 20:07
  • WHERE mycolumn IS NULL Commented Sep 16, 2012 at 20:07

3 Answers 3

Reset to default
4

Column = null will always give null

try column is null

SELECT * FROM `mytable` WHERE mycolumn is null
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Column = null is always false, but right answer.
Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons. From MySQL open docs.
@TonyHopkinson Column = null is unknown not false. SQL uses three valued logic.
@Martin, strange way of looking at it. Would Select * From MyTable Where (Column = null) is null work then? :)
@TonyHopkinson - The distinction is important in check constraints (can evaluate to true or unknown but not false) and also consider negating the original expression. WHERE NOT (mycolumn=null) is NOT(unknown) which is still unknown (as opposed to NOT(false) which would be true). In MySQL SELECT CASE WHEN (null = null) IS NULL THEN 'Y' ELSE 'N' END returns Y. SQL Fiddle
|
1

You have to use 

SELECT * FROM `mytable` WHERE mycolumn is null

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

Improve this answer

Comments

1

In SQL, the concept of null is analagous to unknown. That which is unknown cannot actually be equal to anything, even another unknown.

For this reason, X = null and even null = null will never be true, as the expression itself evaluates to another null (meaning, it is unknown if x is equal to null.)

Instead, you can use IS NULL to figure out if a value is set to null:

SELECT * FROM `mytable` WHERE mycolumn is null

...or you can coalesce a null value into something else.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.