SQLServerCentral / Discuss Content Posted by James Travis / Article Discussions / Article Discussions by Author / Understanding the difference between IS NULL and = NULL / Latest Posts

RE: Understanding the difference between IS NULL and = NULL

gatorspike — Sat, 23 Feb 2008 09:36:18 GMT

You are correct Matt. I guess I was a bit hasty in my remarks. I, like you, do not mess with the ANSI_NULLS option.Mike

RE: Understanding the difference between IS NULL and = NULL

Matt Miller (#4) — Fri, 22 Feb 2008 13:28:46 GMT

Mike - If you happen to set ANSI_NULLS, then it CAN be true....Try this:[code]set ANSI_NULLS OFFgoselect case when NULL=NULL then 0 else 1 endgoset ANSI_NULLS ONgoselect case when NULL=NULL then 0 else 1 endgo set ANSI_NULLS OFF[/code]Of course - IMO messing with ANSI_NULLS is just asking for trouble (and is a deprecated setting, so stop playing with it!!!!!!!!!), but that is an entirely different issue.

RE: Understanding the difference between IS NULL and = NULL

gatorspike — Fri, 22 Feb 2008 13:19:59 GMT

I am calling Bull%&!$ on this one because it starts wrong - big time. Heck it was not even tested.[b]Try this:[/b] (The first three statements are from the article)DECLARE @val CHAR(4)SET @val = NULLIf @val = NULL select 1else select 0You get [b]0[/b]. Nothing is = to null. not even null.[b]Try this:[/b]If NULL = NULL select 1else select 0You get [b]0[/b][b]Try this:[/b]DECLARE @val2 CHAR(4)DECLARE @val3 CHAR(4)SET @val2 = NULLSET @val3 = NULLIf @val2 = @val3 select 1else select 0You get [b]0[/b]You cannot ask for equals to null ever. You must ask if it [b]is null[/b].

RE: Understanding the difference between IS NULL and = NULL

stefanbeeli — Fri, 22 Feb 2008 08:17:06 GMT

Let me preface these statements with "IN GENERAL"Using Null values in a database is just bad design.It adds a third state to the value of a variable. (=, <>, IsNull)Good database designers will create a database that doesn't contains Nulls whereever possible. I've been doing this for years and I don't have a Null issue. There may be other applications that require it, but in most real-world business programming it's never an issue.Just say no to NULL. It makes life much easier.

RE: Understanding the difference between IS NULL and = NULL

Arnie Stewart — Fri, 22 Feb 2008 08:11:30 GMT

I was going to add a reply very similar to that made by Grasshopper, but in that he did, I will just say that I have do the same and found it to be very trust worthy.Arnie

RE: Understanding the difference between IS NULL and = NULL

mmcginty — Fri, 22 Feb 2008 03:09:58 GMT

Interesting article, but I'm afraid your comments about C++ are not technically accurate:[quote]In C++ when a variable is created the variable has an address of 0xddddddd (in debug but it can be different non-real addresses as well). When you set the variable the first time checking the address will give you a valid memory address where the data is being stored.[/quote]It can be easily proven that this is not the case. In C++ a variable has both an address and storage space upon declaration. In debug mode that storage is initialized with a distinctive pattern (such as 0xdd, depending on compiler version.) In release mode, it will contain whatever random garbage happened to be in memory.This is true of both pointer types and non pointer types (in the strictest sense) though pointer types require a bit more explanation: A pointer type, as its name suggests, is a variable that stores an address that points to something else. When a pointer is declared, it is true that no memory has been allocated to store anything [i]of the type it points to[/i], that space must be separately allocated, and its address is assigned to the pointer. Even so, at declaration a pointer type does have an address of it's own, and enough memory to store a [i]pointer[/i] has been allocated for it.cout.flags(ios::hex);int i; // sizeof(int) bytes allocated on the stackint *p; // size of a memory address allocated on the stackcout << i << endl; // garbage valuecout << (int)&i << endl; // valid mem locationcout << (int)p << endl; // garbage valuecout << *p << endl; // access fault! can't dereference invalid locationcout << (int)&p << endl; // valid mem location (double indirection)p = &i; // storage for an int now assigned to pointercout << *p << endl; // value is still garbage, but valid to defererence*p = 1; // assigning value to mem pointed tocout << i << endl; // pop quiz: what will this output be? :-)-Mark McGinty

RE: Understanding the difference between IS NULL and = NULL

ben012453 — Tue, 29 Jan 2008 18:27:35 GMT

S, >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. The vtable contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<This statement appears to make sense to me. However I can only speak from a logical programming standpoint:-[b]There is a table to hold references to variables for the declare statement [/b](vtable)- [b]vtable[/b] - NAME MEMORY ADDRESS- [b]Since a table can contain nulls it would stand that the vtable can have nulls for MEMORY ADDRESS.[/b]- [b]When a variable is created with a declare statement the vtable gets the NAME you gave it and a null for the MEMORY ADDRESS [/b]??? (needs verification, I dont know if this is true)[quote]NAME | MEMORY ADDRESS----------------| ID | NULL |----------------[/quote]- [b]When a variable is assigned a value THEN it gets a memory address that points to that value.[/b] ??? (needs verification, I dont know if this is true)[quote]NAME | MEMORY ADDRESS---------------------------| ID | 9001232412321 |---------------------------[/quote]Again this is subjectory siince I am a SQL NEWB and am only writing this from reading the statement you have posted above. But it would seem to be the case.-SQL NEWB

RE: Understanding the difference between IS NULL and = NULL

ben012453 — Tue, 29 Jan 2008 18:19:23 GMT

Stephen Baez, I believe this is what you are looking for:SELECT COUNT(*) AS TotalRows, COUNT(id) AS NonNULLRows, COUNT(*) - COUNT(id) AS NULLRows FROMModify to fit your database. COUNT(*) does exactly that, it returns ALL ROWS of a table. -SQL NEWB

RE: Understanding the difference between IS NULL and = NULL

S-322532 — Thu, 24 Jan 2008 03:07:06 GMT

When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. [color=#ff#00#00][b]The vtable contains the name and memory address of the variable[/b][/color]. [color=#00#ff#00]However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory[/color].Hi,Out of confusion aroused with the above two statements (highlighted )contradicting. Can any one explain them.

RE: Understanding the difference between IS NULL and = NULL

Rajni Kant Ranjan — Wed, 22 Aug 2007 23:54:00 GMT

Thanks Travis to provide interesting topics like that before that i was very confuse about NULL.Thanks ................................Excellent jobs

RE: Understanding the difference between IS NULL and = NULL

Carl Federl — Sat, 29 Oct 2005 08:53:00 GMT

It is a SQL Server BUG that is fixed in Service Pack 4FIX: A parallel query may return unexpected resultshttp://support.microsoft.com/default.aspx?scid=kb;en-us;814509SYMPTOMSWhen the following conditions are met, a parallel query may return unexpected results: The query uses a parallel nonclustered index scan in the execution plan. The query contains an IS NULL condition in the WHERE clause of the query.

RE: Understanding the difference between IS NULL and = NULL

Stephen Baez — Sat, 29 Oct 2005 07:42:00 GMT

I am seeing something strange. When I do a count(*) from table where column IS NULL, my result is every row in the table. This is a 46 million row table and I know that not every row contains a null in the the specific column that I am testing for NULL. I know that there are about 2 million that do. If I do a select top 3000000 * into #temp where column is NULL, I get my 2 million records inserted into the temp table. But why will count(*) not show me a count of 2 million? Does SQL Server have problem with large tables in this regard? Thanks!

RE: Understanding the difference between IS NULL and = NULL

Mike C — Mon, 06 Jun 2005 19:41:00 GMT

Per the ANSI-92 standard:

1. COUNT(*) returns the cardinality of a table.

2. COUNT(column) applies the value expression to all rows of the table, then eliminates all rows where column is NULL.

SQL Uses Three-valued logic, as describes in more detail here: http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp

RE: Understanding the difference between IS NULL and = NULL

Kenneth Lee — Mon, 06 Jun 2005 18:55:00 GMT

"COUNT(*), on the other hand, does not eliminate NULLs."

Actually, COUNT(val) will always eliminate nulls, but * includes all fields in the table and by definition is not null and will not be eliminated from the count.

I also noticed I said

Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'

Should have said

Can be 'isnull(customerid,0) = isnull(' & sCustomerID & ',0)'

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 22:59:00 GMT

As pointed out previously, COUNT(column1) eliminates NULLs from the final result set, per the ANSI SQL-92 standard. COUNT(*) does not. Try this instead:

Select Count(*), PremiumGroup From RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 22:57:00 GMT

When 'ON', both tests return 'False'. When 'OFF', both tests return 'True'.This is the case for both SQL 7 and 2000. Try it for yourselves!

With ANSI_SQL ON, both return Unknown, not False. Try it for yourself:

SET ANSI_NULLS [ON|OFF]DECLARE @val CHAR(4)IF @val = NULL print 'True'ELSE IF NOT(@val = NULL) print 'False'ELSE print 'Unknown'SET @val = NULL If @val = NULL print 'True'ELSE IF NOT(@val = NULL) print 'False'ELSE print 'Unknown'

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 22:53:00 GMT

"Select Count(Field1) Where Field1 Is Null" DOES return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

No, it does not. SELECT COUNT(column1) eliminates NULL values, per the ANSI-92 definition. SELECT COUNT(*) does not.

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 22:51:00 GMT

"Select Count(Field1) Where Field1 Is Null" should return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

The problem is with the ANSI definition of COUNT() and other aggregate functions. COUNT(column_name) by definition counts all the rows that match your WHERE clause, and then eliminates all NULLs. COUNT(*), on the other hand, does not eliminate NULLs.

Try this:

SELECT COUNT(*) WHERE Column1 IS NULL

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 22:39:00 GMT

SQL uses Three-Valued Logic; it is not tied to the Two-Valued Logic you have forced on it in this article. Your comparison of @val = NULL does not result in FALSE. It results in UNKNOWN. You are being misled by the fact that you are printing 'FALSE' to the screen after the initial comparison. This is how IF works in SQL:

IF (a = b) PRINT 'TRUE'ELSE PRINT 'FALSE'

In the example, if a equals b is True, then 'TRUE' is printed on the screen; otherwise, 'FALSE' is printed on the screen. For two-valued logic (i.e., C++, VB, etc. logic) the result can be only True or False; so using ELSE as a catch-all for anything other than True does not present a problem. However, in three-valued logic this presents a problem which can be demonstrated here:

IF (a = b) PRINT 'TRUE'ELSE IF NOT(a = b) PRINT 'FALSE'ELSE PRINT 'UNKNOWN'

In this case, if a or b is NULL, the result of comparison is neither True nor False; it is Unknown. The example above will print UNKNOWN if a = b results in Unknown. Three-valued logic requires three comparisons to determine the exact result of the expression as you can see from the above.

RE: Understanding the difference between IS NULL and = NULL

Mike C — Fri, 03 Jun 2005 18:50:00 GMT

The article is *wrong* on a few major points. I will submit a full article detailing the problems and supplying the correct information this weekend.

RE: Understanding the difference between IS NULL and = NULL

Kenneth Lee — Fri, 03 Jun 2005 16:43:00 GMT

Another place to watch out for

... WHERE field NOT IN (SELECT field2 FROM tbl)

If field2 ever has a null value, this will always result in no selections. It's funny, if that is never true you would think 'IN' would allways be true, but that one just compares the values.

RE: Understanding the difference between IS NULL and = NULL

Kenneth Lee — Fri, 03 Jun 2005 16:36:00 GMT

FYI sql like 'customerid = ' & sCustomerID

Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'

or it can be '(customerid is null and ' & sCustomerID & ' is null) or customerid = ' & sCustomerID

RE: Understanding the difference between IS NULL and = NULL

sara karasik — Fri, 03 Jun 2005 09:36:00 GMT

Familiarize yourself with the SQL function isnull().

When comparing a field which may have a null value, instead of = CustomerID

use = isnull(CustomerID, '')

which means: If the customerid is null, treat it as '', an empty string.

Good Luck

RE: Understanding the difference between IS NULL and = NULL

Victor Virrueta — Fri, 03 Jun 2005 08:59:00 GMT

hi!!, i was playing around with my QA and tryin' to find the perfect query to avoid this NULL issues. Of course, i still haven't found it.

I think this is like... try to explain what means nothing (in Real Life ).

SET ANSI_NULLS [Off | on]

DECLARE @val1 intDECLARE @val2 int

If (isnull(@val1,null) = isnull(@val2,null)) print 'True'else print 'False'

SET @val1 = NULLSET @val2 = NULL If (isnull(@val1,null) = isnull(@val2,null)) print 'True'else print 'False'

Well i know this could not happen in real cases, but is curious how the result of this is always False, why??, i'm not expert in SQL Server but.. for SET ANSI_NULLS Off .. the result shouldn't be TRUE???

can anybody explain this??..

thanks!!

RE: Understanding the difference between IS NULL and = NULL

Kelvin Lush — Fri, 03 Jun 2005 08:10:00 GMT

Thanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.

RE: Understanding the difference between IS NULL and = NULL

Chris Stamey — Fri, 03 Jun 2005 08:09:00 GMT

I just ran into this yesterday, and could not get a count for Null values if I was using the field with the Nulls in it for the count(), regardless of the ANSI Nulls setting. If I used a different field, such as the ID field, in the count(), I did get the count correctly.

The query:Select Count(PremiumGroup), PremiumGroup From RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup

The results:0 NULL27 A94 B124 C32 D345 E193 F16 G195 New LA7 Special

There are actually over 2000 records with a Null in that field.

But in any case, Null can be undetermined value, value not assigned, absence of value, etc. but in reality it is the same thing, no value, and should be recognized as its own special value to make logic easier when programming.

I am unable to imagine how making Nulls easier to deal with could make life more difficult for any of us, or our logic.

Thanks,Chris

RE: Understanding the difference between IS NULL and = NULL

Antares686 — Fri, 03 Jun 2005 07:57:00 GMT

I see this. When I tested wrote and tested this I am fairly sure I used QA (not sure if was 7 or 2000 thou) and against multiple 7 instances but it would have been pre SP4 on SQL 7. If I get a chance I will probe into what changed the effect on SQL 7 and when. But at the time of the articles write up that was the effect you would get. Just one additional reason IS NULL is a better choice because if there was a fundamental chage in evaluation then your expected results may not be what you got after whatever changed this. It'll probably be a month before I can test thou as I am in the middle of a big project.

RE: Understanding the difference between IS NULL and = NULL

Kelvin Lush — Fri, 03 Jun 2005 07:36:00 GMT

WHY CAN'T NULL = NULL?????????????

Because NULL in SQL 92 is taken to mean Unknown Value, not NO Value. If the value is unknown, how can you say that it equals anything?

That is why any test for '= NULL' will fail.

Also, re your comment about using IS NULL, "Select Count(Field1) Where Field1 Is Null" DOES return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

RE: Understanding the difference between IS NULL and = NULL

Chris Stamey — Fri, 03 Jun 2005 07:30:00 GMT

WHY CAN'T NULL = NULL?????????????

That has always been my biggest problem in dealing with Nulls. Null does equal Null in the real world. If Null = the absence of a value, and you have two instances of the absence of a value, they are the same, Null.

If the field value is Null, the absence of a value in that field, IT EQUALS NULL!!!

I just don't get why it has to be so complicated. Humans write the software and we can make it evaluate Nulls any way we want to, so why can't it just be simple, Null = Null, "Is Null" should be the same thing as "= Null", but not the same thing as "= 'Null'".

"Select Count(Field1) Where Field1 Is Null" should return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

I think we could all get along just fine knowing that Null is a special value (or lack of) that is different than 'Null' (string of letters) but Null = Null.

Chris

RE: Understanding the difference between IS NULL and = NULL

Kelvin Lush — Fri, 03 Jun 2005 02:34:00 GMT

I agree with axeld1980.

The statements made about setting a value to null rather than just not setting it are incorrect. It makes absolutely no difference whether the value is assigned to null or not.

The results you get are wholly dependent on the value for ANSI_NULLS. With this set to ON, nothing will ever '= NULL', not even if it is set explicitly. With the setting OFF, both '= NULL' and 'IS NULL' behave in the same way. So:

SET ANSI_NULLS [ON|OFF]DECLARE @val CHAR(4)If @val = NULL print 'True'else print 'False'SET @val = NULL If @val = NULL print 'True'else print 'False'

When 'ON', both tests return 'False'. When 'OFF', both tests return 'True'.This is the case for both SQL 7 and 2000. Try it for yourselves!

RE: Understanding the difference between IS NULL and = NULL

David le Quesne — Fri, 03 Jun 2005 00:48:00 GMT

The Borland Delphi application I administer uses a SQL Server 2000 back end with ANSI NULLS set to ON. If I remove a date from a field in the application, it doesnt return to NULL, it returns to a non-NULL value which is not displayed, so I have to explicitly set the value to NULL if the user removes the date otherwise my IS NULL comparisons will not work

David le Quesne

RE: Understanding the difference between IS NULL and = NULL

zhudawei — Thu, 19 Aug 2004 20:17:00 GMT

It is very hard to remember the rules. Of course we can always use 'is null' in sql however the problem is in asp code sometime you have to make up the sql like 'customerid = ' & sCustomerID. In this case, it's also too much work to change '=' to 'is' when sCustomerID is NULL

RE: Understanding the difference between IS NULL and = NULL

JayTKay — Fri, 20 Jun 2003 21:11:00 GMT

Too lazy to remember the rules about nulls and ANSI NULLS and empty strings, so I avoid tripping over them.Typically, I use comparisons like this:isnull(@variable,'') = ''isnull(@variable,0) = 0coalesce(@variable,@anothervariable,'') = ''

RE: Understanding the difference between IS NULL and = NULL

sunshinekid — Mon, 16 Dec 2002 02:18:00 GMT

alexd1980, you probably have ansi nulls turned on. In this case NULL=NULL always evaluates to unknown, so your if expression evaluates the ELSE branch. This is why it doesn't matter what the value of your variable is.

RE: Understanding the difference between IS NULL and = NULL

alexd1980 — Sun, 15 Dec 2002 19:45:00 GMT

Strange:I issue thisDECLARE @val CHAR(4)If @val = NULL select 'Yup1'else select 'Nop1'SET @val = NULLIf @val = NULL select 'Yup2'else select 'Nop2'there is a result: ---- Nop1(1 row(s) affected) ---- Nop2(1 row(s) affected)?

RE: Understanding the difference between IS NULL and = NULL

sunshinekid — Fri, 13 Dec 2002 04:16:00 GMT

Anyone interested in digging further should check out BOL. Its simplest to look for 'NULL Comparison Search Conditions' in the index & follow the link to 'Null Values' at the bottom of the article (its part of the expression syntax).Null behaviour is documented quite well in these sections, although previously my info has mostly been gleaned from empirical research & helping those with broken code. This area is a classic example of where changing a server default can break your code quite horribly:BP - always use 'set ansi nulls on' for each session WP1 - forget to check the server setting WP2 - assume behaviour is the same across versions

RE: Understanding the difference between IS NULL and = NULL

don1941 — Thu, 12 Dec 2002 07:54:00 GMT

This was my first hard lesson learned in SQL. I've never forgotten it these past 8-9 years. I think every programming standards guide ought to mention this (as a running footer on every printed page!) because it's the #1 question i still get on every project.

RE: Understanding the difference between IS NULL and = NULL

Simon Sabin — Thu, 12 Dec 2002 05:50:00 GMT

The other aspect to be aware is that NULL + 'hello world' = NULL in SQL 7 on wards without ansi nulls offSimon SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

RE: Understanding the difference between IS NULL and = NULL

Antares686 — Thu, 12 Dec 2002 04:23:00 GMT

Hey SunshineI looked all over and did find all the details I wanted, so I appreciate that additional information. I wrote the article as I keep seeing this issue show up in questions on the forums. I based my information of course on a basic understanding of C++ and the way the variables react in the situations I could directly affect. However, the information you add here, can you point me to where you found this.

RE: Understanding the difference between IS NULL and = NULL

sunshinekid — Thu, 12 Dec 2002 03:32:00 GMT

I realised the topic specifically covered variables, but some of these aspects also apply more widely to any expression involving null. Under sql92 any expression involving null directly evaluates to unknown, rather than true/false, hence the issues with relational operators. Operators like IS NULL explicitly convert back to 2-valued logic so you can do some sensible boolean algebra. Note that different versions of t-sql have used different implementations of = (and possibly other operators) when used with NULL. In early versions of sqlserver (thosed based on Sybase, up to 6.0 I think), selecting values from a table using = NULL used the ANSI meaning and never matched any records. This has since changed and with ansi nulls off you will get 'matching' records.