RE: IS NULL VS = NULL
This is expected. =NULL will never work as NULL is not a value, it isn't anything really. - and therefore it cannot be equal to anything. You always have to use IS NULL. -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: 09 February 2006 10:27 To: CF-Talk Subject: IS NULL VS = NULL SQL Server - I had a situation where I needed to make sure a field was NULL. So I used WHERE the field = NULL. It didn't return any records, but it should've. So I changed it to WHERE thefield IS NULL, and it works fine. Why is this? What's the difference between = NULL and IS NULL? Thanks, Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231715 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: IS NULL VS = NULL
Hey Will... I've experienced the same thing and would be curious to know as well... I've just chalked it up to just being a pure syntax thing... nothing more than that's just the way it is in SQL server... let's see if anybody can clarify Tim -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 5:27 AM To: CF-Talk Subject: IS NULL VS = NULL SQL Server - I had a situation where I needed to make sure a field was NULL. So I used WHERE the field = NULL. It didn't return any records, but it should've. So I changed it to WHERE thefield IS NULL, and it works fine. Why is this? What's the difference between = NULL and IS NULL? Thanks, Will ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231721 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: IS NULL VS = NULL
I'm guessing that it is just a syntax thing...somewhere in the SQL Server engine, it makes it easier for them to handle the comparison. Oddly, the update isn't like that...when you set a column to NULL, you use col_name = NULL, not something like col_name TO BE NULL On the subject, here is a pretty good article about why NULL should be avoided http://www.aspfaq.com/show.asp?id=2073 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231724 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: IS NULL VS = NULL
You might have missed it.. **This is expected** =NULL will never work as NULL is not a value, it isn't anything really. - and therefore it cannot be equal to anything. You always have to use IS NULL. -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: 09 February 2006 14:20 To: CF-Talk Subject: Re: IS NULL VS = NULL I'm guessing that it is just a syntax thing...somewhere in the SQL Server engine, it makes it easier for them to handle the comparison. Oddly, the update isn't like that...when you set a column to NULL, you use col_name = NULL, not something like col_name TO BE NULL On the subject, here is a pretty good article about why NULL should be avoided http://www.aspfaq.com/show.asp?id=2073 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231725 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: IS NULL VS = NULL
Hi, I'm guessing that it is just a syntax thing...somewhere in the SQL Server engine, it makes it easier for them to handle the comparison. well, as Neil already said, NULL is not a value and thus can not be compared to any other value or field content. NULL actually means the field content is not existing. Taken from MySQL documentation: [quote]In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.[/quote] And a little further: [quote] If you want to search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true for any expression: mysql SELECT * FROM my_table WHERE phone = NULL;[/quote] Oddly, the update isn't like that...when you set a column to NULL, you use col_name = NULL, not something like col_name TO BE NULL No offense intended, but the difference between assingment and comparison should be obvious. This is absolutely not just the way it is in SQL server, but pure database and SQL fundamentals. The ppl. who developed SQL by no way made it like this by mistake or by accident. It was meant to be this way, and not just in SQL server. Again, absolutely no offense intended, but, if NULLs are unclear to you guys, I recommend you get yourself a good book on database basics, unless you want to run into some serious trouble with databases sooner or later. Best, Chris ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231726 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: IS NULL VS = NULL
Maybe if you SET the filed to the string 'NULL' but NULL has never been tested by '=' that I've seen. It's not a value. I doubt an update would change that. If it does, I'll be extremely ticked off as I'm changing tons of queries :-) ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 9:20 AM To: CF-Talk Subject: Re: IS NULL VS = NULL I'm guessing that it is just a syntax thing...somewhere in the SQL Server engine, it makes it easier for them to handle the comparison. Oddly, the update isn't like that...when you set a column to NULL, you use col_name = NULL, not something like col_name TO BE NULL On the subject, here is a pretty good article about why NULL should be avoided http://www.aspfaq.com/show.asp?id=2073 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231727 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: IS NULL VS = NULL
Ahh I see you meant an update QUERY not an update for sql server. At any rate... setting a column with '=' in an update isnt the same as testing for equality and '=' is the ONLY way to set a columns value in an update. ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 9:43 AM To: CF-Talk Subject: RE: IS NULL VS = NULL Maybe if you SET the filed to the string 'NULL' but NULL has never been tested by '=' that I've seen. It's not a value. I doubt an update would change that. If it does, I'll be extremely ticked off as I'm changing tons of queries :-) ...:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 9:20 AM To: CF-Talk Subject: Re: IS NULL VS = NULL I'm guessing that it is just a syntax thing...somewhere in the SQL Server engine, it makes it easier for them to handle the comparison. Oddly, the update isn't like that...when you set a column to NULL, you use col_name = NULL, not something like col_name TO BE NULL On the subject, here is a pretty good article about why NULL should be avoided http://www.aspfaq.com/show.asp?id=2073 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231741 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: IS NULL VS = NULL
OK, I guess I stepped on a nerve here...and in retrospect, I should have thought out my post more -I should not have made a reference to SQL Server...the IS NULL operator is part of the ANSI-92 standard, and would be used in any database that conforms to that(but the original post was referring to SQL Server :-)) . But the question was why IS NULL...it is a syntax thing...the IS operator is used for nothing else that I know of. It allows the database engine to override the rule that NULL = NULL resolves to Unknown and actually do the comparison, treating NULL as a value (value is in quotes so no one jumps down my throat, going...NULL has no value!!!...that is understood). -The assignment issue is not completely obvious to meand this is more of an overall issue with the ANSI-92 standard than with assignment vs comparison...in some ways, I think having a special syntax for the assignment of NULL would have helped people understand the concept. I also don't think database systems should default fields to allow NULL if it is not specified(as SQL Server does). -This is part of the SQL Standard, and IS probably the best way to handle NULLs, but there are exceptions...that is why you have conditions like SET ANSI_NULLS OFF. The fact is that the concept of three value logic is not that straight forward to everyone, and including it in the SQL spec has probably confused a lot of people. -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231745 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: IS NULL VS = NULL
It's proper SQL Willsimple as that. ;-) The reason is NULL is nothing...and something cannot equal nothingbut it can be NULL...so we sqay IS NULL or IS NOT NULL. Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231751 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: IS NULL VS = NULL
Thanks Bryan! That makes sense. :) Will hehe...if you're looking for a non-technical answer...ask meI shy away from jargon...never know if the other person gets what you're laying down ;-) Cheers Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231843 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
