March 15, 2018 at 7:59 am
dmbaker - Thursday, March 15, 2018 6:49 AMMMartin1 - Wednesday, March 14, 2018 10:35 PMYou cannot compare a value to a null. It is an illegal operation.Of course you can compare a value to NULL. It's not illegal at all. Dumb, maybe, and the result might be unexpected if you don't know the rules, but it's certainly not illegal. And you could always set ANSI_NULLS to make it work -- though I think that generally is kinda dumb too (especially since Microsoft is warning that they are deprecating it). Better to learn/know the rules and abide by them, though, I think.
In formal logic it is, in a sense, "illegal". NULL is the absence of value. So you're trying to compare nothing to something, which really isn't logical. That's why "WHERE A = NULL" isn't a valid construction. A comparison with NULL to any value is never really true or false, because it can't be, it will always be unknown. Since a WHERE conditions requires a true value to pass, unknown fails, and the row isn't selected. It doesn't mean that unknown somehow becomes false.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 15, 2018 at 8:12 am
ScottPletcher - Thursday, March 15, 2018 7:59 AMIn formal logic it is, in a sense, "illegal". NULL is the absence of value. So you're trying to compare nothing to something, which really isn't logical. That's why "WHERE A = NULL" isn't a valid construction. A comparison with NULL to any value is never really true or false, because it can't be, it will always be unknown. Since a WHERE conditions requires a true value to pass, unknown fails, and the row isn't selected. It doesn't mean that unknown somehow becomes false.
Yes, thank you I know the rules. I was just being pedantic. To me "illegal" means that you simply can't do it at all (e.g. it throws an error). The fact remains that you *can* compare a value to NULL. "WHERE A = NULL" is certainly a "valid construction" in the language (it won't produce a syntax error). And if you futz with the ANSI_NULLS setting, it's not only valid, it'd work. I still think it's dumb to do that though.
March 15, 2018 at 11:35 am
dmbaker - Thursday, March 15, 2018 8:12 AMScottPletcher - Thursday, March 15, 2018 7:59 AMIn formal logic it is, in a sense, "illegal". NULL is the absence of value. So you're trying to compare nothing to something, which really isn't logical. That's why "WHERE A = NULL" isn't a valid construction. A comparison with NULL to any value is never really true or false, because it can't be, it will always be unknown. Since a WHERE conditions requires a true value to pass, unknown fails, and the row isn't selected. It doesn't mean that unknown somehow becomes false.Yes, thank you I know the rules. I was just being pedantic. To me "illegal" means that you simply can't do it at all (e.g. it throws an error). The fact remains that you *can* compare a value to NULL. "WHERE A = NULL" is certainly a "valid construction" in the language (it won't produce a syntax error). And if you futz with the ANSI_NULLS setting, it's not only valid, it'd work. I still think it's dumb to do that though.
You cannot compare a value to an absence of value. These are two different things. It is like multiplying 3 * infinity, the latter is not a number but an idea (similar to what NULL is). In math my expression in not a valid one. I am not going to get hung up on wording (legal/valid/ whatever) as I think I make the point clear.
----------------------------------------------------
March 15, 2018 at 11:49 am
sgmunson - Thursday, March 15, 2018 6:27 AMMMartin1 - Wednesday, March 14, 2018 10:35 PM
select case when NOT( 1 = null ) then 1 else 0 end
select case when ( 1 = null ) then 1 else 0 endBoth return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).And you are looking solely at a CASE statement evaluation, which does allow NULL to propagate all the way up, but as it has an ELSE clause, finally resolves the NULL. It's similar, but different to the case of the WHERE clause, which requires a binary decision at the end of all the evaluations. Your CASE statement example simply reproduces the binary decision by implementing an ELSE.
Drawing off your example :
SELECT 1 WHERE NOT (NULL = 1)
SELECT 1 WHERE (NULL = 1)
Both return the same thing Why? My statement above on what NULL is applies everywhere. Here there are never true values encountered so no results can be returned. You can say that the expression HANDLES the invalid operation of comparing a value to a non value without creating an error.
----------------------------------------------------
March 15, 2018 at 11:59 am
MMartin1 - Thursday, March 15, 2018 11:35 AMYou cannot compare a value to an absence of value. These are two different things. It is like multiplying 3 * infinity, the latter is not a number but an idea (similar to what NULL is). In math my expression in not a valid one. I am not going to get hung up on wording (legal/valid/ whatever) as I think I make the point clear.
Yes, thank you, I know the rules. I'm not (and haven't been) talking about math. I'm talking about TSQL, where the blanket statement "you cannot compare a value to an absence of a value" does not hold true. I *can* compare a value to NULL in T-SQL. I'm making no claim that such a comparison will produce a meaningful/correct result of course.
March 15, 2018 at 1:47 pm
dmbaker - Thursday, March 15, 2018 11:59 AMMMartin1 - Thursday, March 15, 2018 11:35 AMYou cannot compare a value to an absence of value. These are two different things. It is like multiplying 3 * infinity, the latter is not a number but an idea (similar to what NULL is). In math my expression in not a valid one. I am not going to get hung up on wording (legal/valid/ whatever) as I think I make the point clear.Yes, thank you, I know the rules. I'm not (and haven't been) talking about math. I'm talking about TSQL, where the blanket statement "you cannot compare a value to an absence of a value" does not hold true. I *can* compare a value to NULL in T-SQL. I'm making no claim that such a comparison will produce a meaningful/correct result of course.
I have to still disagree. You can make an expression that looks like it will create or does create a comparison, but such a thing does not occur in SQL or in Math. SQL is an implementation of logic so you cannot separate the two.
----------------------------------------------------
March 15, 2018 at 1:53 pm
MMartin1 - Thursday, March 15, 2018 1:47 PMdmbaker - Thursday, March 15, 2018 11:59 AMMMartin1 - Thursday, March 15, 2018 11:35 AMYou cannot compare a value to an absence of value. These are two different things. It is like multiplying 3 * infinity, the latter is not a number but an idea (similar to what NULL is). In math my expression in not a valid one. I am not going to get hung up on wording (legal/valid/ whatever) as I think I make the point clear.Yes, thank you, I know the rules. I'm not (and haven't been) talking about math. I'm talking about TSQL, where the blanket statement "you cannot compare a value to an absence of a value" does not hold true. I *can* compare a value to NULL in T-SQL. I'm making no claim that such a comparison will produce a meaningful/correct result of course.
I have to still disagree. You can make an expression that looks like it will create or does create a comparison, but such a thing does not occur in SQL or in Math. SQL is an implementation of logic so you cannot separate the two.
No but logic can have special rules for special cases. Technically you can't take the square root of a negative number, well that was inconvenient, so they invented i for that case that has it's own special rules. Same applies to NULL when you do a comparison against a NULL value the result is neither true nor false.
So you can most certainly do a comparison against NULL it just has special rules you have to be aware of.
March 16, 2018 at 7:44 am
MMartin1 - Thursday, March 15, 2018 11:49 AMsgmunson - Thursday, March 15, 2018 6:27 AMMMartin1 - Wednesday, March 14, 2018 10:35 PM
select case when NOT( 1 = null ) then 1 else 0 end
select case when ( 1 = null ) then 1 else 0 endBoth return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).And you are looking solely at a CASE statement evaluation, which does allow NULL to propagate all the way up, but as it has an ELSE clause, finally resolves the NULL. It's similar, but different to the case of the WHERE clause, which requires a binary decision at the end of all the evaluations. Your CASE statement example simply reproduces the binary decision by implementing an ELSE.
Drawing off your example :
SELECT 1 WHERE NOT (NULL = 1)
SELECT 1 WHERE (NULL = 1)
Both return the same thing Why? My statement above on what NULL is applies everywhere. Here there are never true values encountered so no results can be returned. You can say that the expression HANDLES the invalid operation of comparing a value to a non value without creating an error.
I'm pretty sure that I said that the WHERE clause does indeed make a binary decision, and the net result is that it "HANDLES" the NULL value, effectively turning "unknown" into the functional equivalent of false. I may not have used those exact words, but that was the entire gist of what I was saying. The WHERE clause HAS to make a decision. It can't simply give up and NOT make a decision, As long as people are aware of the distinction between the WHERE clause's need for a final binary decision and the entirely separate evaluation of a comparison with NULL, they are good to go. Arguing over the semantics isn't going to change the reality.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 16, 2018 at 8:21 am
sgmunson - Friday, March 16, 2018 7:44 AMMMartin1 - Thursday, March 15, 2018 11:49 AMsgmunson - Thursday, March 15, 2018 6:27 AMMMartin1 - Wednesday, March 14, 2018 10:35 PM
select case when NOT( 1 = null ) then 1 else 0 end
select case when ( 1 = null ) then 1 else 0 endBoth return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).And you are looking solely at a CASE statement evaluation, which does allow NULL to propagate all the way up, but as it has an ELSE clause, finally resolves the NULL. It's similar, but different to the case of the WHERE clause, which requires a binary decision at the end of all the evaluations. Your CASE statement example simply reproduces the binary decision by implementing an ELSE.
Drawing off your example :
SELECT 1 WHERE NOT (NULL = 1)
SELECT 1 WHERE (NULL = 1)
Both return the same thing Why? My statement above on what NULL is applies everywhere. Here there are never true values encountered so no results can be returned. You can say that the expression HANDLES the invalid operation of comparing a value to a non value without creating an error.I'm pretty sure that I said that the WHERE clause does indeed make a binary decision, and the net result is that it "HANDLES" the NULL value, effectively turning "unknown" into the functional equivalent of false. I may not have used those exact words, but that was the entire gist of what I was saying. The WHERE clause HAS to make a decision. It can't simply give up and NOT make a decision, As long as people are aware of the distinction between the WHERE clause's need for a final binary decision and the entirely separate evaluation of a comparison with NULL, they are good to go. Arguing over the semantics isn't going to change the reality.
WHERE makes a binary decision in that it includes a row or it doesn't. But that does not mean that WHERE is conflating NULL into FALSE. The final decision may simply be that only TRUE includes a row, that FALSE or NULL do not.
As shown earlier, SQL does a similar thing with CHECK constraints. Only a FALSE constraint prevents the row from being processed; NULL does not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 16, 2018 at 9:30 am
ScottPletcher - Friday, March 16, 2018 8:21 AMsgmunson - Friday, March 16, 2018 7:44 AMMMartin1 - Thursday, March 15, 2018 11:49 AMsgmunson - Thursday, March 15, 2018 6:27 AMMMartin1 - Wednesday, March 14, 2018 10:35 PM
select case when NOT( 1 = null ) then 1 else 0 end
select case when ( 1 = null ) then 1 else 0 endBoth return the same value, 0. You cannot compare a value to a null. It is an illegal operation. Sql server cannot return a response. Not because it is a known false, but because it is a unknown ... == > Neither are TRUE.
I think we may be overly complicating this. To me something being FALSE means it has been determined, not the same as NULL (undetermined).And you are looking solely at a CASE statement evaluation, which does allow NULL to propagate all the way up, but as it has an ELSE clause, finally resolves the NULL. It's similar, but different to the case of the WHERE clause, which requires a binary decision at the end of all the evaluations. Your CASE statement example simply reproduces the binary decision by implementing an ELSE.
Drawing off your example :
SELECT 1 WHERE NOT (NULL = 1)
SELECT 1 WHERE (NULL = 1)
Both return the same thing Why? My statement above on what NULL is applies everywhere. Here there are never true values encountered so no results can be returned. You can say that the expression HANDLES the invalid operation of comparing a value to a non value without creating an error.I'm pretty sure that I said that the WHERE clause does indeed make a binary decision, and the net result is that it "HANDLES" the NULL value, effectively turning "unknown" into the functional equivalent of false. I may not have used those exact words, but that was the entire gist of what I was saying. The WHERE clause HAS to make a decision. It can't simply give up and NOT make a decision, As long as people are aware of the distinction between the WHERE clause's need for a final binary decision and the entirely separate evaluation of a comparison with NULL, they are good to go. Arguing over the semantics isn't going to change the reality.
WHERE makes a binary decision in that it includes a row or it doesn't. But that does not mean that WHERE is conflating NULL into FALSE. The final decision may simply be that only TRUE includes a row, that FALSE or NULL do not.
As shown earlier, SQL does a similar thing with CHECK constraints. Only a FALSE constraint prevents the row from being processed; NULL does not.
I think the thread spent too much time trying to justify the following text, when we really should just never considered that a comparison to null could ever return any sort of value that could be considered "false". The best we should have assumed is that a comparison to null would return whatever equated evaluated to null in the context of conditional expressions.
So like "snopes" does, I gotta rate the below as false.
Don't really want to quibble over semantics, but I think it may be more clear to say that a COMPARISON to NULL always returns false unless you are testing for NULL values by using IS NULL (the two word variety, not the ISNULL function). It's the act of comparison that returns the boolean value, and not the NULL value itself.
Check the following conditions for instance:
select case when 1 = null then 'true' else 'false' end
select case when not(1 = null) then 'true' else 'false' end
select case when not(not(1 = null)) then 'true' else 'false' end
select case when not(not(not(1 = null))) then 'true' else 'false' end
The closest we can really say about the "expression" 1 = null is that if it evaluates to ANYTHING, the only thing it makes sense to say is that it evaluates to null because look at that thing, null will just not go away right? There is clearly a "truth value" that is not getting inverted and I gotta say, when I think that null "propagates" outward from the innermost expression to the outermost expression that is then used as a conditional clause, its gonna act just like the above sql acts.
There may be languages that treat nulls like sgmunson says, but T-SQL doesn't look like one of them, so I gotta add my vote for scotts analysis. 2 cents!
(edited for terminology)
March 16, 2018 at 11:52 am
MMartin1 - Thursday, March 15, 2018 1:47 PMdmbaker - Thursday, March 15, 2018 11:59 AMMMartin1 - Thursday, March 15, 2018 11:35 AMYou cannot compare a value to an absence of value. These are two different things. It is like multiplying 3 * infinity, the latter is not a number but an idea (similar to what NULL is). In math my expression in not a valid one. I am not going to get hung up on wording (legal/valid/ whatever) as I think I make the point clear.Yes, thank you, I know the rules. I'm not (and haven't been) talking about math. I'm talking about TSQL, where the blanket statement "you cannot compare a value to an absence of a value" does not hold true. I *can* compare a value to NULL in T-SQL. I'm making no claim that such a comparison will produce a meaningful/correct result of course.
I have to still disagree. You can make an expression that looks like it will create or does create a comparison, but such a thing does not occur in SQL or in Math. SQL is an implementation of logic so you cannot separate the two.
You really have to separate the two. If you try to apply a purely mathematical analysis to T-SQL then you might get into trouble. In T-SQL, "null" says that there is no value stored. It doesn't say the value is unknown, it doesn't in fact imply anything other than the determination that there is no value returned because no value exists in that expression, whether stored or evaluated to. The act of programming deals with machine representation of logic, you cannot say that math demands that logic act this way without ensuring that programming text gets interpreted or compiled into a form that in fact duplicates the actions that your math specifies.
Its much like numerical analysis for instance, computers can only approximate reality in a mathematical sense, and you often have to rearrange the representation of your mathematical operations in such a way as to lower errors to a level acceptable to the actual model you are trying to represent on the computer.
March 16, 2018 at 12:15 pm
patrickmcginnis59 10839 - Friday, March 16, 2018 11:52 AMYou really have to separate the two. If you try to apply a purely mathematical analysis to T-SQL then you might get into trouble. In T-SQL, "null" says that there is no value stored. It doesn't say the value is unknown, it doesn't in fact imply anything other than the determination that there is no value returned because no value exists in that expression, whether stored or evaluated to. The act of programming deals with machine representation of logic, you cannot say that math demands that logic act this way without ensuring that programming text gets interpreted or compiled into a form that in fact duplicates the actions that your math specifies.Its much like numerical analysis for instance, computers can only approximate reality in a mathematical sense, and you often have to rearrange the representation of your mathematical operations in such a way as to lower errors to a level acceptable to the actual model you are trying to represent on the computer.
Agreed overall. I try to take a practical approach as well. But I have to disagree when someone ways that "NULL" becomes "FALSE", because I think that's too big a stretch and could even actively disrupt people who are learning SQL.
Another issue in SQL is that NULL must be both "no value exists (or is applicable)" and "value exists but is unknown" (such as a birthdate: there must be one, but you just don't it for this person). Using separate "markers" to distinguish between those two in SQL was debated, but it's just a well they didn't do that either. One NULL is tricky enough to deal with.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply