a<>b OR not(a=b)
-
What should I use between a<>b and not(a=b)?
-
If you view the query plan for not(a=b) vs for a<>b you will see that the predicate is evaluated as the same thing. The CPU cost is the same for both of these, but since not(a=b) gets evaluated as a<>b in the predicate, I would imagine you will see the most miniscule performance increase using a<>b. They are effectively the same thing, and I can't imagine there would ever be a situation where using exactly those two statements would give you any kind of significant performance increase one way or another.
[edit]Thought it was worth mentioning though that depending on what you are doing, it may be "prettier" and easier to read to use one or the other in the actual code.[/edit]
-
I agree with tnolan. There will very a very slight performance increase with <>, however I prefer that one for the reason that was added at the end...
If I'm looking down through a whole list of predicates in my where clause, a <> b just seems a whole lot easier to read than NOT(a=b). This becomes particularly true in something like the following...
SELECT *
FROM LongTableName inner join longtablename2 on col1 = col2
WHERE LongTableName.LongColumnNAme <> 1
SELECT *
FROM LongTableName inner join longtablename2 on col1 = col2
WHERE NOT LongTableName.LongColumnNAme = 1
Those are 2 identical queries, but if I'm troubleshooting why it's not returning the results I'm expecting, I'm going to be focused on what the column is set equal to and not focused on the NOT before the column name.
But that's just my $.02, just how my mind works... There are some who I'm sure would recommend the other for the same reasons because that's the way their mind works...
-Luke.
-
That is a perfect example of what I was talking about with being easier to read. I actually missed the NOT at first glance looking at your code! 😀
-
Thanks everyone!
I personally prefer a<>b, but we have a senior programmer at work who loves not(a=b).....not(Is Null).....not(whatever). I can't just say it's difficult to debug the code with not(....) because he would tell me to prove that not(a=b) is worse than (a<>b) performance wide.
Oh well...I will loose this battle, won't I?
-
I guess it depends on your organization...
If you're the DBA and you're in charge of code that is executed against your database, I'd assume you would have the ability to dictate style. And you could also toss the question right back to him about performance. Ask him to prove to you it's faster his way, it is your database and you're responsible for the code etc. etc...
If not then perhaps you may be sunk.
Or you could bring up the point that since SQL Server needs to translate it from NOT a=b to a <> b before it executes the code that there is a performance impact, albeit a very minuscule one.
Plus there's the tack similar to I'm not a programmer I'm a DBA, but every programming book I've read said that yes you could do it this way, but that using negative logic was something to be avoided for readability, maintainability and all of the reasons already mentioned.
-Luke.
-
I still miss the most obvious question ....
Does your data contain NULL and how does that relate to the results you expect from your queries?
FYI
www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/
www.sqlservercentral.com/articles/Advanced+Querying/2829/
www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/
www.sqlservercentral.com/articles/Miscellaneous/nullfunctionsarentthosemeetings/1313/
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
jungnaja (3/4/2009)
Thanks everyone!I personally prefer a<>b, but we have a senior programmer at work who loves not(a=b).....not(Is Null).....not(whatever). I can't just say it's difficult to debug the code with not(....) because he would tell me to prove that not(a=b) is worse than (a<>b) performance wide.
Oh well...I will loose this battle, won't I?
Heh... lemme guess... (s)he also likes leading commas in a SELECT list, too, huh? 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think that you could argue with your "senior programmer" that "a <> b" is more readable and maintanable and is thus the preferable method. A "senior programmer" should understand that readability and long-term maintainability of code is a serious concern. Especially given that said programmer may not be the one who has to maintain that code in the future.
If it's an ego thing though, then I think you're out of luck unless you can out-ego them.
-
I think that you could argue with your "senior programmer" that "a <> b" is more readable and maintanable and is thus the preferable method. A "senior programmer" should understand that readability and long-term maintainability of code is a serious concern. Especially given that said programmer may not be the one who has to maintain that code in the future.
I agree with this if you talking about single criteria, but sometimes you wish to exclude rows when a set of criteria are true. In this case I think that:
WHERE NOT (Col1 = 'Value1' AND Col2 = 'Value2')is clearer than
WHERE (Col1 <> 'Value1' OR Col2 <> 'Value2')In fact I have seen a couple of examples on this forum where the posters were trying to use the second of the styles above and got the logic wrong, using AND when they should have used OR.
-
I certainly don't disagree, in fact I think you're in line with the point I was trying to make. That is, prefer readability and maintainability if possible. You may not be the one who has to maintain your code in the future, and even if you are, you may find that code you wrote in the past -- your own code -- is incomprehensible to you. Be nice to the people who have to work with your crap when you're gone. 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply