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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Sourabh
  • 1,757
  • 6
  • 21
  • 43
  • 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 – dash Sep 16 '12 at 20:07
  • `WHERE mycolumn IS NULL` – Glenn Sep 16 '12 at 20:07

3 Answers3

4

Column = null will always give null

try column is null

SELECT * FROM `mytable` WHERE mycolumn is null
Dharman
  • 30,962
  • 25
  • 85
  • 135
Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • 1
    Column = null is always false, but right answer. – Tony Hopkinson Sep 16 '12 at 20:09
  • 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. – Dumitrescu Bogdan Sep 16 '12 at 20:11
  • 2
    @TonyHopkinson `Column = null` is `unknown` not `false`. SQL uses three valued logic. – Martin Smith Sep 16 '12 at 20:11
  • @Martin, strange way of looking at it. Would Select * From MyTable Where (Column = null) is null work then? :) – Tony Hopkinson Sep 16 '12 at 20:28
  • @TonyHopkinson actually it is working .. check the fiddle : http://sqlfiddle.com/#!2/a2581/1734/0 – Dumitrescu Bogdan Sep 16 '12 at 20:33
  • 1
    @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](http://sqlfiddle.com/#!2/d41d8/2044) – Martin Smith Sep 16 '12 at 20:34
  • It's a good day I learnt something. A why more than a how, but still good. – Tony Hopkinson Sep 17 '12 at 01:51
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

dominik
  • 5,745
  • 6
  • 34
  • 45
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.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326