The difference between T-SQL ISNULL and = NULL
Filed under T-SQL
One of my colleagues and I have been trying to find out exactly what the difference between using ISNULL (or NVL for you Oracle people) and (var) = NULL in our WHERE clause.
For example:
SELECT a
FROM tblB
WHERE a = NULL
What I've come to realize is the two clauses return different query results. I was taught to use the ISNULL function in SQL, but it was returning different results between my databases and his.
If someone could verify my theory, but here's what I think is happening: (var) = NULL is defaulting the test condition to a default value, depending on the variable type. So for string type variables, it is testing (var) = "". From my days of programming in C++, the value "" and NULL are two seperate things. NULL is supposed to be undefined, meaning you don't know what it could be. "" on the other hand is actually defining it is as something; a blank value.
So which is the correct way? It really depends on how you setup your database and how you Insert the data in your database. If your database is initializing the fields as "", then = NULL is the right way. If you use the keyword NULL, then ISNULL will correctly test for it. Just something to keep in mind if you work with diffent databases or work with someone with a different programming style.
Dec12