Another SQL gotcha

Posted At : October 13, 2005 9:22 AM | Posted By : Trond Ulseth
Related Categories: SQL

Yesterday I was working on the sample app for a 'soon to be announced' new framework. I needed to query to get all records with a null value in a numerical field.

Here's what I tried at first:

SELECT *
FROM forum_messages
WHERE msgParentID = NULL

Problem was that it did not return any rows, but also did not throw any error. For a second I beleived that it must be something in the my application code that was wrong, but I threw the code into the SQL Query Analyzer and still got zero rows returned.

Of course the answer was not further away than a quick Google search, and what I found was that I needed to change my SQL to this:

SELECT *
FROM forum_messages
WHERE msgParentID IS NULL

If you are interested in the why of this you can read about it here

Comments

[Add Comment]

I don't know why, maybe I read it somewhere, maybe its because the first query I looked at (that was looking for NULL) did it that same way, but I have always done ...WHERE column IS NULL'

# Posted By Scott Stroz | 10/13/05 1:09 PM

Someone once told me that Null means "don't know", so that always helps me think through SQL involving Nulls. Null, "don't know", can never be equal (=) to anything.

# Posted By fred wenger | 10/13/05 5:55 PM

I don't know about your DB, but MySQL has a <=> operator which works exactly like the = operator, except it considers NULL equal to itself. Better to use IS NULL in this case, but if you're doing a query against that column, and don't know if the value coming is will be NULL or not, <=> is a lifesaver:

where col <=> <cfqueryparam ... null="#NOT isNumeric(myVar)#" />

Without that operator, you have to do this:

<cfif isNumeric(myVar)>
where col = <cfqueryparam ... />
<cfelse>
where col IS NULL
</cfif>

# Posted By Barney | 10/13/05 6:14 PM

[Add Comment]