1

Possible Duplicate:
IS NULL vs = NULL in where clause + SQL Server

Consider the following code – can someone explain to me why the one query doesn’t throw an error? I can understand why it doesn’t return a record, but what is it trying to do? Remember that colA is an int. It works the same on 2000,2005 and 2008 r2

create table #foo
( colA int )


insert into #foo
(colA)
values
(null)

select * from #foo --returns the one record we just created

select * from #foo where colA = null --does not throw an error and does not return a record! why??
select * from #foo where colA is null --returns the record

drop table #foo

Is there ever record that could exist in this table that would return for colA = null?

I dont have any other databases available to me at the moment to try this out - is this standard or is it behavior specific to MSSQL?

Community
  • 1
  • 1
Ryan Guill
  • 13,558
  • 4
  • 37
  • 48
  • as a side question, if anyone has a better suggestion for a title for this question let me know - I struggled with how to put it. – Ryan Guill Aug 31 '11 at 19:28
  • This has been addressed multiple times before... – JNK Aug 31 '11 at 19:36
  • http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server – JNK Aug 31 '11 at 19:36
  • http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null – JNK Aug 31 '11 at 19:36
  • http://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false – JNK Aug 31 '11 at 19:37
  • http://stackoverflow.com/questions/7078837/why-doesnt-sql-support-null-instead-of-is-null – JNK Aug 31 '11 at 19:37
  • 1
    @JNK - The OP is asking why the syntax doesn't prohibit it. Not for an explanation of the behaviour. – Martin Smith Aug 31 '11 at 19:38
  • ..and -1 for not searching for something like `SQL SERVER NULL` which will turn up 5 dupes in the first page. – JNK Aug 31 '11 at 19:38
  • 1
    While extremely similar, the question here is "Why doesn't it throw an error?' – dee-see Aug 31 '11 at 19:38
  • Yet the answers are the same: **NULL IS A NON-VALUE**. You have to allow `=` with `NULL` if you allow mixed `NULL` and non-`NULL` fields. – JNK Aug 31 '11 at 19:39
  • 1
    @JNK - But you don't have to allow it for the literal `NULL`. With `ANSI_NULLS ON` it could cause a compile time error same as dividing by the literal `0`. – Martin Smith Aug 31 '11 at 19:41
  • Although of course my divide by 0 point is a bad one as that would cause a run time error and it would be very odd to have something that caused an error at compile time but was allowed at run time. – Martin Smith Aug 31 '11 at 19:53

5 Answers5

5

That is because a NULL can't be equated to any value.

Disable the ANSI_NULLS option and then run it you will see the row now:

SET ANSI_NULLS OFF
select * from #foo --returns the one record we just created  
select * from #foo where colA = null --does not throw an error and does not return a record! why?? 
select * from #foo where colA is null --returns the record  drop table #foo
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • 1
    I think its also interesting to note that SET ANSI_NULL is to be [removed from future version of sql server](http://msdn.microsoft.com/en-us/library/ms143729.aspx) (and not just deprecated like in SQL Server 2008). So the usage of `=` against a `NULL` is not recommanded. – Pierre-Alain Vigeant Aug 31 '11 at 19:34
4

You can force the behavior by using SET ANSI_NULLS OFF; but since this is deprecated and makes the code rely on a SET setting that can't always be guaranteed, the real solution is to use the right syntax (the one that returns the row).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3

It does not throw an error because it is not an invalid query. The problem is that NULL represents the absence of data and you can't have anything that is equal to that. So when you do

select * from #foo where colA = null

it does try to find rows where colA = null, but that's just impossible. Therefore no errors and no results.

dee-see
  • 23,668
  • 5
  • 58
  • 91
  • 2
    Yes. I suppose it is not invalid (even with `ANSI_NULLS ON` where it makes no sense) for the same reason as `WHERE 1=2` does not throw an error. – Martin Smith Aug 31 '11 at 19:34
2

NULL is a special value in RDBMS that means an unknown or inapplicable value.

So nothing will ever be = NULL; such comparison is not valid, thus any queries with expr = NULL in their WHERE clause will return no rows.

Of course this has its quirks and some implementations do things differently; eg: Oracle treats empty strings ('') as NULL and gets quite a bit grief for that.

SQL Server has the ANSI NULL parameter that makes it compliant with SQL-92 (eg comparisons with NULLs like you did won't work.

NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • More along the lines of `(anything = null)` evaluates to null, so effectively, the where clause is literally seen as `... WHERE null`. – Marc B Aug 31 '11 at 20:22
1

I can't tell you why and for what reason the = operator does not support null values but in mysql there is another operator that does: "<=>" Only works in mysql though.

Markus
  • 2,174
  • 2
  • 22
  • 37