SQLServerCentral.com / Discuss Content Posted by James Travis / Article Discussions / Article Discussions by Author / Understanding the difference between IS NULL and = NULL / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:10:18 GMT20RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxYou are correct Matt. I guess I was a bit hasty in my remarks. I, like you, do not mess with the ANSI_NULLS option.MikeSat, 23 Feb 2008 09:36:18 GMTgatorspikeRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxMike - 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.Fri, 22 Feb 2008 13:28:46 GMTMatt Miller (#4)RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxI 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].Fri, 22 Feb 2008 13:19:59 GMTgatorspikeRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxLet 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. (=, &lt;&gt;, 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.Fri, 22 Feb 2008 08:17:06 GMTstefanbeeliRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxI 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.ArnieFri, 22 Feb 2008 08:11:30 GMTArnie StewartRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxInteresting 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 &lt;&lt; i &lt;&lt; endl; // garbage valuecout &lt;&lt; (int)&i &lt;&lt; endl; // valid mem locationcout &lt;&lt; (int)p &lt;&lt; endl; // garbage valuecout &lt;&lt; *p &lt;&lt; endl; // access fault! can't dereference invalid locationcout &lt;&lt; (int)&p &lt;&lt; endl; // valid mem location (double indirection)p = &i; // storage for an int now assigned to pointercout &lt;&lt; *p &lt;&lt; endl; // value is still garbage, but valid to defererence*p = 1; // assigning value to mem pointed tocout &lt;&lt; i &lt;&lt; endl; // pop quiz: what will this output be? :-)-Mark McGintyFri, 22 Feb 2008 03:09:58 GMTmmcgintyRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxS, &gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;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 &lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;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 NEWBTue, 29 Jan 2008 18:27:35 GMTben012453RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxStephen 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 NEWBTue, 29 Jan 2008 18:19:23 GMTben012453RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxWhen 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.Thu, 24 Jan 2008 03:07:06 GMTS-322532RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxThanks Travis to provide interesting topics like that before that i was very confuse about NULL.Thanks ................................Excellent jobsWed, 22 Aug 2007 23:54:00 GMTRajni Kant RanjanRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxIt 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. Sat, 29 Oct 2005 08:53:00 GMTCarl FederlRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxI 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!Sat, 29 Oct 2005 07:42:00 GMTStephen BaezRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>Per the ANSI-92 standard:</P><P>1. COUNT(*) returns the cardinality of a table.</P><P>2. COUNT(column) applies the value expression to all rows of the table, then eliminates all rows where column is NULL.</P><P>SQL Uses Three-valued logic, as describes in more detail here: <A href="http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp">http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp</A></P>Mon, 06 Jun 2005 19:41:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>"COUNT(*), on the other hand, does not eliminate NULLs."</P><P>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. </P><P>I also noticed I said </P><P><SPAN>Can be 'isnull(customerid) = isnull(' &amp; sCustomerID &amp; ')'</SPAN></P><P><SPAN>Should have said</P><P><SPAN>Can be 'isnull(customerid,0) = isnull(' &amp; sCustomerID &amp; ',0)'</SPAN></P></SPAN>Mon, 06 Jun 2005 18:55:00 GMTKenneth LeeRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>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:</P><P>Select Count(*), PremiumGroup From RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup</P>Fri, 03 Jun 2005 22:59:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P><STRONG>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!</STRONG></P><P>With ANSI_SQL ON, both return Unknown, not False. Try it for yourself:</P><P><FONT face="Courier New">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'</FONT></P>Fri, 03 Jun 2005 22:57:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P><STRONG>"Select Count(Field1) Where Field1 Is Null" <U>DOES</U> return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.</STRONG></P><P>No, it does not. SELECT COUNT(column1) eliminates NULL values, per the ANSI-92 definition. SELECT COUNT(*) does not.</P><P> </P>Fri, 03 Jun 2005 22:53:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P><STRONG>"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.</STRONG></P><P>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.</P><P>Try this:</P><P>SELECT COUNT(*) WHERE Column1 IS NULL</P>Fri, 03 Jun 2005 22:51:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>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:</P><P>IF (a = b) PRINT 'TRUE'ELSE PRINT 'FALSE'</P><P>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:</P><P>IF (a = b) PRINT 'TRUE'ELSE IF NOT(a = b) PRINT 'FALSE'ELSE PRINT 'UNKNOWN'</P><P>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. </P>Fri, 03 Jun 2005 22:39:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxThe article is *wrong* on a few major points. I will submit a full article detailing the problems and supplying the correct information this weekend.Fri, 03 Jun 2005 18:50:00 GMTMike CRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>Another place to watch out for</P><P>... WHERE field NOT IN (SELECT field2 FROM tbl)</P><P>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.</P>Fri, 03 Jun 2005 16:43:00 GMTKenneth LeeRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P><SPAN id=Showtread1_ThreadRepeater__ctl10_lblFullMessage>FYI sql like 'customerid = ' &amp; sCustomerID</SPAN></P><P><SPAN>Can be 'isnull(customerid) = isnull(' &amp; sCustomerID &amp; ')'</SPAN></P><P><SPAN>or it can be '(customerid is null and ' &amp; sCustomerID &amp; ' is null) or customerid = ' &amp; sCustomerID</SPAN></P><P><SPAN></SPAN> </P>Fri, 03 Jun 2005 16:36:00 GMTKenneth LeeRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>Familiarize yourself with the SQL function isnull().</P><P>When comparing a field which may have a null value, instead of = CustomerID</P><P>use =<STRONG> isnull(CustomerID, '') </STRONG></P><P>which means: If the customerid is null, treat it as '', an empty string.</P><P>Good Luck</P><P> </P>Fri, 03 Jun 2005 09:36:00 GMTsara karasikRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>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. <img src='images/emotions/blink.gif' height='20' width='20' border='0' title='Blink' align='absmiddle'></P><P>I think this is like... try to explain what means nothing (in Real Life <img src='images/emotions/biggrin.gif' height='20' width='20' border='0' title='Big Grin' align='absmiddle'>). </P><P>SET ANSI_NULLS [Off | on]</P><P>DECLARE @val1 intDECLARE @val2 int</P><P>If (isnull(@val1,null) = isnull(@val2,null)) print 'True'else print 'False'</P><P>SET @val1 = NULLSET @val2 = NULL If (isnull(@val1,null) = isnull(@val2,null)) print 'True'else print 'False'</P><P>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???</P><P>can anybody explain this??.. </P><P>thanks!!</P><P> </P>Fri, 03 Jun 2005 08:59:00 GMTVictor VirruetaRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxThanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.<img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>Fri, 03 Jun 2005 08:10:00 GMTKelvin LushRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>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.</P><P>The query:Select Count(PremiumGroup), PremiumGroup From RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup</P><P>The results:0 NULL27 A94 B124 C32 D345 E193 F16 G195 New LA7 Special</P><P>There are actually over 2000 records with a Null in that field.</P><P>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.</P><P>I am unable to imagine how making Nulls easier to deal with could make life more difficult for any of us, or our logic.</P><P>Thanks,Chris</P>Fri, 03 Jun 2005 08:09:00 GMTChris StameyRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxI 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.Fri, 03 Jun 2005 07:57:00 GMTAntares686RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>WHY CAN'T NULL = NULL?????????????<HR>Because NULL in SQL 92 is taken to mean <EM>Unknown Value</EM>, <STRONG>not</STRONG> <EM>NO Value</EM>. If the value is unknown, how can you say that it equals <EM>anything</EM>?<P></P><P>That is why <EM>any</EM> test for '= NULL' will fail.</P><P>Also, re your comment about using IS NULL, "Select Count(Field1) Where Field1 Is Null" <STRONG><U>DOES</U></STRONG> return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.</P>Fri, 03 Jun 2005 07:36:00 GMTKelvin LushRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>WHY CAN'T NULL = NULL?????????????</P><P>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.</P><P>If the field value is Null, the absence of a value in that field, IT EQUALS NULL!!!</P><P>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'".</P><P>"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.</P><P>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.</P><P>Chris</P>Fri, 03 Jun 2005 07:30:00 GMTChris StameyRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>I agree with axeld1980.</P><P>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.</P><P>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:</P><P><HR>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'<HR>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!</P>Fri, 03 Jun 2005 02:34:00 GMTKelvin LushRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspx<P>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</P><P>David le Quesne</P>Fri, 03 Jun 2005 00:48:00 GMTDavid le QuesneRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxIt 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 = ' &amp; sCustomerID. In this case, it's also too much work to change '=' to 'is' when sCustomerID is NULLThu, 19 Aug 2004 20:17:00 GMTzhudaweiRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxToo 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,'') = '' Fri, 20 Jun 2003 21:11:00 GMTJayTKayRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxalexd1980, 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. Mon, 16 Dec 2002 02:18:00 GMTsunshinekidRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxStrange: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)? Sun, 15 Dec 2002 19:45:00 GMTalexd1980RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxAnyone 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 <img src=icon_smile_evil.gif border=0 align=middle>WP1 - forget to check the server setting <img src=icon_smile_wink.gif border=0 align=middle>WP2 - assume behaviour is the same across versions <img src=icon_smile_dissapprove.gif border=0 align=middle> Fri, 13 Dec 2002 04:16:00 GMTsunshinekidRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxThis 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. Thu, 12 Dec 2002 07:54:00 GMTdon1941RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxThe 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/1904347088Thu, 12 Dec 2002 05:50:00 GMTSimon SabinRE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxHey Sunshine<img src=icon_smile_wink.gif border=0 align=middle>I 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. Thu, 12 Dec 2002 04:23:00 GMTAntares686RE: Understanding the difference between IS NULL and = NULLhttp://www.sqlservercentral.com/Forums/Topic8731-80-1.aspxI 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. Thu, 12 Dec 2002 03:32:00 GMTsunshinekid