OR Evaluations
-
When SQL Server evaluates (in a WHERE clause) something like:
"TeamID IS NULL OR TeamID = @ID"
...are both expressions evaluated for TRUE, or will it stop at the first expression if it is TRUE?
Thanks.
--Lenard
-
It will stop if first expression is true.
-
Great. I was hoping for that.
I thought the following would evaluate to true if @areaDesignator and AreaDesignatorID were NULL...but of course it doesn't:
WHERE AreaDesignatorID = ISNULL( @baseDesignatorID, AreaDesignatorID )One way I thought to fix it was change all my NULLs in AreaDesignatorID to say -1. But because of your reply, performance shouldn't be an issue if I were to do something like:
WHERE AreaDesignatorID IS NULL OR
AreaDesignatorID = ISNULL( @areaDesignatorID, AreaDesignatorID )....right?
--Lenard
-
I did not under stand what you want to do here
WHERE AreaDesignatorID IS NULL OR AreaDesignatorID = ISNULL( @areaDesignatorID, AreaDesignatorID )
ISNULL( @areaDesignatorID, AreaDesignatorID )
means when @areaDesignatorID it returns AreaDesignatorID.
Can explain little more your need.
Thanks,
-
Sorry, I started with a simple question and now asking something different. My initial post was due to a problem I was having with ISNULL. Let me use your answer below as a focus point:
>> AreaDesignatorID = ISNULL( @areaDesignatorID, AreaDesignatorID )
>> means when @areaDesignatorID it returns AreaDesignatorID.
You are correct....but what if both @areaDesignatorID and AreaDesignatorID are NULLs? The statement won't evaluate to TRUE. That is my problem. If the above is in a WHERE CLAUSE and ANDed with other expressions, no results would get returned in the SELECT statement.
I can solve my problem by either making sure that AreaDesignatorID is never NULL (ie. represent NULLs with say a "-1" ) or do something like:
(AreaDesignatorID = ISNULL( @areaDesignatorID, AreaDesignatorID ) OR
AreaDesignatorID IS NULL )What I'm asking now is if the latter solution is a better way to "fix" my problem. My first solution would be more efficient but not so clean since I can't use that approach if the field was of VARCHAR type. I hate the considency of doing it two different ways.
Maybe I am splitting hairs (performance-wise) between the two solutions? 🙂
--Lenard
P.S. The WHERE CLAUSE's define my search form's criteria.
-
quote:
You are correct....but what if both @areaDesignatorID and AreaDesignatorID are NULLs? The statement won't evaluate to TRUE. That is my problem. If the above is in a WHERE CLAUSE and ANDed with other expressions, no results would get returned in the SELECT statement.
I can solve my problem by either making sure that AreaDesignatorID is never NULL (ie. represent NULLs with say a "-1" ) or do something like:
(AreaDesignatorID = ISNULL( @areaDesignatorID, AreaDesignatorID ) OR
AreaDesignatorID IS NULL )What I'm asking now is if the latter solution is a better way to "fix" my problem. My first solution would be more efficient but not so clean since I can't use that approach if the field was of VARCHAR type. I hate the considency of doing it two different ways.
Perhaps I'm missing something, but wouldn't this be cleaner?
WHERE (AreaDesignatorID = @areaDesignatorID OR COALESCE(@areaDesignatorID, AreaDesignatorID) IS NULL)Getting back to your initial question: if the most likely situation is for @areaDesignatorID to be null, then reverse the order of the clauses, i.e.:
WHERE (COALESCE(@areaDesignatorID, AreaDesignatorID) IS NULL OR AreaDesignatorID = @areaDesignatorID)FWIW, I'm also using COALESCE here rather than ISNULL, as that's both more portable and (to me) understandable for this purpose.
--Jonathan
--Jonathan -
Hi Lenard,
quote:
You are correct....but what if both @areaDesignatorID and AreaDesignatorID are NULLs? The statement won't evaluate to TRUE. That is my problem. If the above is in a WHERE CLAUSE and ANDed with other expressions, no results would get returned in the SELECT statement.now, let's see if I got this in the right way.
Consider (a=b) OR (a=NULL)
Well, (a=NULL) is a constant, eg NULL or UNKNOWN
Now,
TRUE OR UNKNOWN yields TRUE while
FALSE OR UNKNOWN yields UNKNOWN
UNKNOWN OR UNKNOWN, well..as you've said this query will return nothing
Hopefully got it right, this kind of courses I attended some years ago
quote:
I can solve my problem by either making sure that AreaDesignatorID is never NULL (ie. represent NULLs with say a "-1" ) or do something like:...
What I'm asking now is if the latter solution is a better way to "fix" my problem. My first solution would be more efficient but not so clean since I can't use that approach if the field was of VARCHAR type. I hate the considency of doing it two different ways.
I would place a default value on that column, even if it's a varchar. This solution seems cleaner to me and remember it's not all about performance.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
Regarding your last comment, I can't really place a default in a VARCHAR, because say the user leaves it blank on the form.....like say a Postal Code (or City or State) field.
--Lenard
-
Jonathan....I'm not sure if your solution will work. For example:
@areaDesignatorID will only be NULL if the user doesn't want to search by that option on the search form. So, if @areaDesignator is NULL and the database field (ie. AreaDesignator) happens to have a value other NULL, your solution would evaluate to FALSE, right?
I need it to evaluate to TRUE because I need to short-circuit that expression out since it is just 1 of 4 options that the user can search on. My WHERE CLAUSE has to work whether the user is using one or all 4 search criteria parameters on the search form.
--Lenard
-
quote:
Regarding your last comment, I can't really place a default in a VARCHAR, because say the user leaves it blank on the form.....like say a Postal Code (or City or State) field.What about
- n/a
- Choose one (Choose you city)
- ...
Plenty of possibilities, I guess.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
Yes, if you are selecting from a list.....However, I'm really talking about non-list items.....like postal codes which the user enters and will be saved to a VARCHAR field. If the user leaves it blank, you can't just put a default value in that field because your searches would not be very accurate.
I suppose I could use set my "default value" as an emptry string.....can this be done with Enterprise Manager? I couldn't figure it out when I was in the table design mode.
--Lenard
-
quote:
Jonathan....I'm not sure if your solution will work. For example:@areaDesignatorID will only be NULL if the user doesn't want to search by that option on the search form. So, if @areaDesignator is NULL and the database field (ie. AreaDesignator) happens to have a value other NULL, your solution would evaluate to FALSE, right?
I need it to evaluate to TRUE because I need to short-circuit that expression out since it is just 1 of 4 options that the user can search on. My WHERE CLAUSE has to work whether the user is using one or all 4 search criteria parameters on the search form.
--Lenard
Yes, I thought I might be missing something.
I thought you were trying to only return rows where the column's value matched the parameter, even if the parameter was null. I personally don't like this "clever" solution using a "one size fits all" SP for searches. Unless you specify WITH RECOMPILE it will often have the wrong plan cached, and if you do specify WITH RECOMPILE, you might as well just build the correct query dynamically in the SP or use IF...ELSE. I see smart people recommend this method, though, so evidently I'm somewhat iconoclastic.
The code you need is:
WHERE (AreaDesignatorID = ISNULL(@areaDesignatorID, AreaDesignatorID) OR ISNULL(AreaDesignatorID, @areaDesignatorID) IS NULL)quote:
Yes, if you are selecting from a list.....However, I'm really talking about non-list items.....like postal codes which the user enters and will be saved to a VARCHAR field. If the user leaves it blank, you can't just put a default value in that field because your searches would not be very accurate.The only time I use nullable columns is for cases like this where the value actually is unknown. Then the built-in logic will be strictly correct, even if you are forced to deal with the resultant ternary predicates.
quote:
I suppose I could use set my "default value" as an emptry string.....can this be done with Enterprise Manager? I couldn't figure it out when I was in the table design mode.I don't use EM for DDL, but here's the statement:
ALTER TABLE LensTable ADD DEFAULT '' FOR AreaDesignatorIDAn empty string, however, is not the same as unknown. There are countries with no postal code system, so a blank value is correct and known, and should not be confused with "unknown", i.e. NULL.
--Jonathan
--Jonathan -
Hi Jonathon,
Thanks for your post. Regarding your code:
WHERE (AreaDesignatorID = ISNULL(@areaDesignatorID, AreaDesignatorID) OR ISNULL(AreaDesignatorID, @areaDesignatorID) IS NULL)Why use 2 ISNULLs when
WHERE (AreaDesignatorID = ISNULL(@areaDesignatorID, AreaDesignatorID) OR AreaDesignatorID IS NULL)will work? Isn't the latter more efficient?
Regarding blank defaults......thanks for the tip. And yes I know that a blank is not the same as a NULL. However, in my case, any fields that are left blank on the user form are saved as NULLs in the database. In C# when you do a .ToString() on a DataRow object, an empty string is returned and that is what is assigned to the textbox on the form.
--Lenard
-
quote:
Hi Jonathon,Thanks for your post. Regarding your code:
WHERE (AreaDesignatorID = ISNULL(@areaDesignatorID, AreaDesignatorID) OR ISNULL(AreaDesignatorID, @areaDesignatorID) IS NULL)Why use 2 ISNULLs when
WHERE (AreaDesignatorID = ISNULL(@areaDesignatorID, AreaDesignatorID) OR AreaDesignatorID IS NULL)will work? Isn't the latter more efficient?
It may be more efficient, but it's wrong. That's why I was confused as to your purpose with this query. Your code will return all the rows with null values regardless of the value of the parameter.
quote:
Regarding blank defaults......thanks for the tip. And yes I know that a blank is not the same as a NULL. However, in my case, any fields that are left blank on the user form are saved as NULLs in the database. In C# when you do a .ToString() on a DataRow object, an empty string is returned and that is what is assigned to the textbox on the form.Null is the default for a nullable column. If you don't want or need the special "unknown" meaning of null in your data, then make the column NOT NULL and assign another default. You earlier gave an excellent reason for making the column nullable: the user leaves the value blank, so the value is unknown. I have found that users can be obtuse when it comes to nulls; sometimes I have the first choice "Unknown" and that is saved as null but they can also choose an empty entry when that is a valid value (we hope); I just try to make that require more effort than choosing "Unknown".
--Jonathan
--Jonathan -
Jonathan,
I saw your reply to me on the Topic "help with stored procedure".
Thanks again.
--Lenard
Edited by - lenardd on 10/18/2003 5:18:36 PM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply