I am trying to do a query that sees if fields are equivalent. However, whenever the field is NULL it returns a false result. I even tried doing the same thing with the column itself:
SELECT * FROM `mturk_completion` WHERE (`mturk_completion`.`imdb_url` =
`mturk_completion`.`imdb_url` AND `mturk_completion`.`worker_id` = 'A3NF84Q37D7F35' )
And it only returns results where the column is not NULL. Why is this so, and how do I get around it?
Your title is absolutely correct for any SQL implementation (not just MySQL). NULL is not equal to anything (including another NULL).
You need to use explicit IS NULL check or COALESCE() function (or its RDBMS-dependent alternatives) to set some default value in case of NULL.
Your comparison of mturk_completion.imdb_url to itself is redundant and should always return True, except when mturk_completion.imdb_urlis Null, in which case it will return Null.
That's because the operator = returns either True, False when comparisons can be made or Null, when either of the two operators is Null
Try this to illustrate the situation.
SELECT 1 = NULL; -- returns NULL
SELECT 1 != NULL; -- also return NULL
SELECT ISNULL(1 = NULL); -- returns 1
SELECT ISNULL(1 != NULL); -- returns 1
If you rewrite your query like below, your problems with ignoring NULLs will go away:
SELECT * FROM `mturk_completion` WHERE worker_id = 'A3NF84Q37D7F35'
I think you can use
(table.Field = table2.Field OR COALESCE(table.Field, table2.Field) IS NULL)
mturk_completion.imdb_url=mturk_completion.imdb_url"? As far as I know, the contents of a column will always equal the contents of the same column.NULLvalues. TheNULLvalues don'tequateto otherNULLvalues. The=operator just returns aNULLinstead.