Sql: not in Vs. not Equal

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.

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:

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.

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:

Please continue at Programminghelp.com Thanks and hapy coding!