SQL: NOT IN vs. NOT EQUAL
By: Zack Turnbow
Introduction
While writing SQL queries, have you ever strung several <> (not equals) together in the WHERE clause and just wondered if that was inefficient? Or have you used the NOT IN function, (both of which can be used to get the same result), and wondered the same thing? If you have or just are curious about either of these methods, keep reading. In this article, both of these functions will be explored to see how efficient they are, plus it will cover a few other methods to get the same results.
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Implementation
First, let’s see which is faster, NOT IN or NOT EQUAL. To be able to test this, a dummy table with some data needs to be created. Use the attached SQL script to create a test table. The script will take several minutes to run. After the table has been created, we need to set up the base test script without the query that is to be tested. Use the attached base test query script. Its contents are as follows:
DECLARE @Start datetime, @End datetime -- The start and end time of the query
DECLARE @Iterations int, @Results int -- Total iterations and the results
DECLARE @Counter int -- Loop counter
SET @Iterations = 100
PRINT 'The test has started'
SET @Counter = 0
SET @Start = getDate()
WHILE @Counter < @Iterations
BEGIN
-- Queries go here
SET @Counter = @Counter + 1
END
SET @End = getDate()
PRINT 'Time Elapsed: ' + cast(datediff(ms, @Start, @End) AS varchar) + ' milliseconds'
The actual test query will go inside the WHILE loop. Now we need to create the test queries, one for NOT IN and the other for NOT EQUAL. The two queries are below and can be found in the attached file called TestQueries.
FROM [Dummy]
WHERE ID NOT IN (622, 623, 624, 625)
SELECT @results = count(ID)
FROM [Dummy]
WHERE ID <> 622 AND
ID <> 623 AND
ID <> 624 AND
ID <> 625
Now it is time to test each of the queries, so copy and paste the first query inside the WHILE loop and execute the test. The test will take some time to complete so be patient. Please keep in mind that the execution results posted in this article will vary from the result from a different SQL Server since each machine is different but the final comparison of NOT EQUAL and NOT IN should be the same. The results are as follows:
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
NOT IN:
The test has started
Time Elapsed: 46526 milliseconds
NOT EQUAL:
The test has started
Time Elapsed: 46376 milliseconds
As shown, the difference between the two is extremely small. So it really doesn’t matter which way is used. Perhaps NOT IN would be a better choice since there is less typing involved.
Earlier, it was mentioned that there are ways to get the results quicker than the two functions above. It is possible but it takes a little more tweaking. There are two other ways to improve performance, the first one does not use a WHERE clause but instead uses a UNION and LEFT OUTER JOIN to get the same results. The query looks like this:
FROM [Dummy] LEFT OUTER JOIN (
SELECT 622 AS ID UNION
SELECT 623 UNION
SELECT 624 UNION
SELECT 625 ) AS Temp
ON [Dummy].ID = Temp.ID
WHERE Temp.ID IS NULL
Go ahead and copy and paste it inside the WHILE loop in the test script then run it. The results for this article are as follows:
The test has started
Time Elapsed: 28056 milliseconds
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
As you can see, this query produces the results much quicker. But wait there is still one other way to get the results faster. The query will be using the NOT EXIST statement and looks like this:
FROM [Dummy]
WHERE NOT EXISTS(SELECT * FROM
(
SELECT 622 AS TempID UNION ALL
SELECT 623 UNION ALL
SELECT 624 UNION ALL
SELECT 625 ) AS Temp
WHERE Temp.TempID = [Dummy].ID )
Copy and paste this query into the test script then run it. The results are:
The test has started
Time Elapsed: 19916 milliseconds
This query is even faster. In this article hopefully it has been shown that there are many different ways to get the same results out of SQL Server. Some are better than others. It all depends on how much effort is put into the query.
What have we learned?
- Which is faster between NOT IN and NOT EQUAL
- How to test queries for performance.
- Other ways to improve performance.
Attachments
Leave a Comment
#1 Rune
Posted By: Rune | 4.07.2009 at 6:21 AM
Hi Zack.
I was just wondering which version of SQL Server you used when testing the performance tuning?
I tried the same thing with some tables I have in SQL Server 2005, and the result for the "NOT IN"-statement and the "NOT EXISTS/UNION ALL" are the same. They both use 16 milliseconds.
For all I know this could be a really old entry, since there are no date marker on the article. In that case I apologize.
#2 Angela
Posted By: Angela | 6.04.2009 at 9:27 AM
I tested this out on a SQL Server 2000 database and got the following results:
The test has started
Time Elapsed: 643 milliseconds
The test has started
Time Elapsed: 656 milliseconds
The test has started
Time Elapsed: 283 milliseconds
The test has started
Time Elapsed: 156 milliseconds
The NOT EXIST statement was faster! Thank you for this tip.
***
I used this script to create the "Dummy" table with only an ID field and with 8000 records:
--create "Dummy" table
CREATE TABLE [dbo].[Dummy] (ID int identity (1,1))
INSERT [dbo].[Dummy] DEFAULT VALUES
WHILE SCOPE_IDENTITY() < 8000
INSERT [dbo].[Dummy] DEFAULT VALUES
--Angela
#3 lucky in gambling
Posted By: lucky in gambling | 1.06.2010 at 11:39 PM
I have never been able to do that transfer database task (frustrated), but I would be very happy if I could! With SQL server 2000 I could do a "transfer all objects". My source db is on a web-server to which I have not all rights of cource (I may connect to it). Should I still be able to do the transfer database task? From that server to my local PC?
#4 moda blog
Posted By: moda blog | 3.07.2010 at 3:36 AM
Intimately, the article is really the sweetest on this worthw hile subject. I fit in with your conclusions and will thirstily look forward to your next updates. Just saying thankx will not just be enough, for the brilliant lucidity in your writing. I will immediately grab your feeds to stay privy of any updates. Solid work and much success in your business dealings! Greets, David B..
#5 350-001
Posted By: 350-001 | 3.19.2010 at 5:32 AM
For all I know this could be a really old entry, since there are no date marker on the article. In that case I apologize.
#6 logo design
Posted By: logo design | 4.28.2010 at 1:35 AM
Actually i was looking for this information but found it today...really very good information provided in this article
#7 giochi del casinò in rete
Posted By: giochi del casinò in rete | 5.17.2010 at 2:52 AM
I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information? It is extremely helpful and beneficial to your readers Nice article
#8 wholesale laptop adapter
Posted By: wholesale laptop adapter | 5.21.2010 at 1:58 AM
s inefficient? Or have you used the NOT IN function, (both of which can be used to get the same result), and wondered the same thing? If you have or just are curious about either of these methods, keep reading. In this article, both
#9 wmns air max
Posted By: wmns air max | 5.23.2010 at 4:56 AM
nike air max sale
#10 bad credit car loan
Posted By: bad credit car loan | 5.24.2010 at 7:55 PM
Have you tried scripting the database?. Doing so will create a text file with the necessary SQL needed to create the database, tables, keys, etc. Then you can open the text file and run the script. If you need to get the data you could export the tables to a delimited file and then import that file. You would have to do this for every table though so hopefully there aren't that many.
#11 PL SQL Best Practices with Steven Feuerstein
Posted By: PL SQL Best Practices with Steven Feuerstein | 5.25.2010 at 8:17 AM
Thanks for thorough explanation. Everything seems clear to me, not taking into consideration the fact that I am only a beginner. Can you please tell me where I can find still more sufficient info exactly for beginners? I will be much obliged to you. Thanks in advance.
#12 sql queries
Posted By: sql queries | 6.10.2010 at 2:25 PM
I will immediately grab your feeds to stay privy of any updates. Solid work and much success in your business dealings!
#13 mbt shoes clearance
Posted By: mbt shoes clearance | 6.11.2010 at 3:55 AM
Very interesting post - Might be old news, but it was news to me.
#14 mbt shoes clearance
Posted By: mbt shoes clearance | 6.11.2010 at 3:56 AM
Very interesting post - Might be old news, but it was news to me.
#15 coach outlet stores
Posted By: coach outlet stores | 6.12.2010 at 12:57 AM
It's a precise and informative article. Things are formed symptom less. Get to know lot's of artifact which were unbeknownst to me. It's truly one of the most facilitative article I get ever read. Thanks for to percentage sensing for author from you. Symmetrical I bookmarked this diplomat as asymptomatic for to get supply from it in incoming.
#16 michael jordan shoes
Posted By: michael jordan shoes | 6.18.2010 at 8:52 PM
good post!!thank you
#17 Bottle Openers
Posted By: Bottle Openers | 6.22.2010 at 3:03 AM
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info
#18 dizi izle
Posted By: dizi izle | 6.22.2010 at 7:16 AM
Super-Duper site! I am loving it!! Will come back again - taking you feeds also, Thanks.
#19 auto insurance quotes
Posted By: auto insurance quotes | 6.22.2010 at 9:24 AM
Actually i was looking for this information but found it today...really very good information provided in this article
#20 how to potty train a puppy
Posted By: how to potty train a puppy | 7.16.2010 at 10:24 AM
Thanks I always went for the not in but not any more.
#21 gucci bag
Posted By: gucci bag | 7.18.2010 at 11:00 PM
Its great post to learn about modifyinig css.
#22 fendi bags
Posted By: fendi bags | 7.18.2010 at 11:00 PM
Its great post to learn about modifyinig css.
#23 coach handbags on sale
Posted By: coach handbags on sale | 7.18.2010 at 11:01 PM
Its great post to learn about modifyinig css.Great to learn from this post.
#24 coach bag
Posted By: coach bag | 7.18.2010 at 11:01 PM
Its great post to learn about modifyinig css.Great to learn from this post.
#25 coach purse
Posted By: coach purse | 7.18.2010 at 11:01 PM
Its great post to learn about modifyinig css.Great to learn from this post.
#26 hermes birkin
Posted By: hermes birkin | 7.18.2010 at 11:02 PM
Intimately, the article is really the sweetest on this worthw hile subject. I fit in with your conclusions and will thirstily look forward to your next updates. Just saying thankx will not just be enough, for the brilliant lucidity in your writing. I will immediately grab your feeds to stay privy of any updates. Solid work and much success in your business dealings! Greets, David B.
#27 chanel purses
Posted By: chanel purses | 7.18.2010 at 11:02 PM
Intimately, the article is really the sweetest on this worthw hile subject. I fit in with your conclusions and will thirstily look forward to your next updates. Just saying thankx will not just be enough, for the brilliant lucidity in your writing. I will immediately grab your feeds to stay privy of any updates. Solid work and much success in your business dealings! Greets, David B.
#28 tiffany co
Posted By: tiffany co | 7.21.2010 at 2:35 AM
many people choose Tiffany & Co brand.Now For classic and quality genuine silver jewelry, we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#29 tiffany jewelry
Posted By: tiffany jewelry | 7.21.2010 at 2:35 AM
many people choose Tiffany & Co brand.Now For classic and quality genuine silver jewelry, we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#30 tiffany jewellery
Posted By: tiffany jewellery | 7.21.2010 at 2:35 AM
many people choose Tiffany & Co brand.Now For classic and quality genuine silver jewelry, we present you the hottest Tiffany silver jewelry .get more http://www.tiffanyonsale.com/
#31 smith
Posted By: smith | 7.21.2010 at 4:01 PM
the article is really the sweetest on this worthw hile subject. I fit in with your conclusions and will thirstily look forward to your next updates. Just saying thankx will not just be enough, for the brilliant lucidity in your writing.
#32 official nike jordan shoes
Posted By: official nike jordan shoes | 7.25.2010 at 9:33 AM
now and the day after i will always concern you!
#33 tiffany ring
Posted By: tiffany ring | 7.26.2010 at 6:54 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/
#34 tiffany bracelet
Posted By: tiffany bracelet | 7.26.2010 at 6:55 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/
#35 tiffany necklace
Posted By: tiffany necklace | 7.26.2010 at 6:55 AM
Tiffany On Sale Offer a wide range of fashionable Tiffany & Co silver jewelry, including Necklaces, Pendants, Bracelets, Earrings, Rings and Accessories with fantastic discount.more http://www.tiffanyonsale.com/
#36 gucci bags
Posted By: gucci bags | 7.29.2010 at 11:25 PM
thank you
#37 cam guys
Posted By: cam guys | 7.30.2010 at 9:36 PM
great info, most sites don't give you the finder points of sql.
#38 Adidas Online Shop
Posted By: Adidas Online Shop | 8.04.2010 at 11:58 PM
really good read. keep posting like this!
#39 Louboutin Pumps
Posted By: Louboutin Pumps | 8.05.2010 at 10:41 PM
Thank you for posting the source to this, the···· is very educational.
#40 Louboutin Pumps
Posted By: Louboutin Pumps | 8.05.2010 at 10:46 PM
boring. i have seen these styles before. not original at all.
#41 bailey button ugg boots
Posted By: bailey button ugg boots | 8.05.2010 at 11:32 PM
Great post.I like it!
#42 cheap ugg boot store
Posted By: cheap ugg boot store | 8.06.2010 at 1:41 AM
To appreciate the beauty of life one can relish the works of artists and writers of renaissance period. Be it Da Vinci with the ethereal Mona Lisa, Rembrandt or Monet with classic cardy Water Lillies brought out the essence of life. www.uggsbailey.co.uk/.../sitemap.html clz
#43 junk yards
Posted By: junk yards | 8.07.2010 at 6:55 AM
The ties and relationships you establish with others over the next few weeks are likely to stick, and they are likely to prove significant. Choose carefully which ones you pursue, because you do have options and they are not all equal. That does not mean say no to everyone — it means say yes when you really feel good
#44 Outsourcing SEO Services
Posted By: Outsourcing SEO Services | 8.08.2010 at 9:38 AM
Good information. I really appreciate and extraordinarily enjoyed the article.
#45 danny
Posted By: danny | 8.13.2010 at 12:33 AM
hgrtyy.bloggeneration.fr http://topindiaworld.com/addisonffda/
ahernghdsf.mysimblog.com
#46 bags supplier
Posted By: bags supplier | 8.18.2010 at 10:08 PM
ave you used the NOT IN function, (both of which can be used to get the same result), and wondered the same thing? If you have or just are curious about either of these methods, keep reading. In this article, bot
#47 replica jewelry
Posted By: replica jewelry | 8.20.2010 at 9:59 PM
<a href="http://www.replica-jewelry.net/replica-Cartier-Ring-82-b0.html">cartier panther ring</a> to receive your Social Security <a href="http://www.jewelry-tiffany.com/replica-Jewelry-Tiffany-Rings-8-b0.html">tiffany engagement rings</a> benefits is through direct deposit. <a href="http://www.iwc-watches.org/categories/replica-Armani-Watches-19-b0.html">emporio armani watch</a> A check can be <a href="http://www.imitation-watches.com/replica-watches-Chopard-235-b0.html">replica watches Chopard</a> lost or stolen, but with <a href="http://www.replica-jewelry.net/replica-Vivienne-Westwood-Necklace-69-b0.html">vivienne necklace</a>
#48 air max shoes
Posted By: air max shoes | 8.23.2010 at 2:23 AM
Once we get there, so many wonderful dreams will come true and the pieces of our black nike air max shoes lives will fit together like a completed jigsaw puzzle. http://www.sellnikeairmax.com/
#49 air max shoes
Posted By: air max shoes | 8.25.2010 at 10:35 PM
Once we get there, so many wonderful dreams will come true and the pieces of our black nike air max shoes lives will fit together like a completed jigsaw puzzle. http://www.sellnikeairmax.com/
#50 Ugg
Posted By: Ugg | 8.26.2010 at 6:36 AM
Got any reason to say no to cheap UGG boots? UGG boots that prevailed for years will still warm your frozen toes with the featured sheepskin leather,get more http://www.uggbootuksale.com/
#51 fico score
Posted By: fico score | 8.29.2010 at 1:52 PM
Thanks for posting this! We’re so inundated with information these days that the best way to stand out may be to be profoundly respectful and courteous towards your followers’ time. Besides accidentally turning into a spammer, you should also strive to keep your posts short and sweet, entertaining and informative. Make your followers come to you instead of the other way around and you’ll have a much more loyal and engaging customer base!
#52 cheap nfl jerseys
Posted By: cheap nfl jerseys | 9.02.2010 at 5:21 AM
Couldn't agree more...Had a post already written about this too, (good job i checked around first)never mind.It is a really good tune though.
Here's hoping more labels wise up and do this.
#53 lv josephine
Posted By: lv josephine | 9.02.2010 at 12:58 PM
love www.louisvuittonhouse.com/.../louis-vuitton-j
#54 wholesale nfl jerseys
Posted By: wholesale nfl jerseys | 9.08.2010 at 5:37 AM
Hey guys,
I hope this allowed, I have never used this website before so I wasn't really sure what this was going to do. <a href="http://www.nfljerseyse.com" title="wholesale nfl jerseys">wholesale nfl jerseys</a> So this is just a test post. I really like this forum, it has some excellent discussions that take place.
#55 wholesale nfl jerseys
Posted By: wholesale nfl jerseys | 9.08.2010 at 5:37 AM
Hey guys,
I hope this allowed, I have never used this website before so I wasn't really sure what this was going to do. <a href="http://www.nfljerseyse.com" title="wholesale nfl jerseys">wholesale nfl jerseys</a> So this is just a test post. I really like this forum, it has some excellent discussions that take place.
#56 pandor jewelry
Posted By: pandor jewelry | 9.08.2010 at 8:55 PM
Thanks for sharing the information.