Not In v Not Equal - SQL Server Central

Interesting trick - use a derived table instead of a where clause to filter results.

Not In v Not Equal

By Ken Johnson, 2007/05/29

 

I ran the same series of tests on another occasion and the NOT IN query consistently outperformed the AND <> query. The results regularly go back and forth, much like the heads and tails of a coin toss. So, I have managed to convince myself that, despite the two execution times listed above, these two queries are, indeed, the same query as far as SQL Server is concerned -- at least on this day, on this server, for these queries (I still gravitate toward the comforting ambiguity of "it depends").

I mentioned earlier that there were several queries that outperform our basic AND <> and NOT IN queries (on this server on this day). Let's take a look at some of those queries and their execution results. The first alternative technique doesn't use a WHERE clause to filter out our integer values. It places the integer values into a UNION query and does a LEFT OUTER JOIN against that to filter out unequal rows. Here is what that query looks like:

   SELECT @results = count(filterCriterion_sv) 
   FROM tbl_IN_VS_AND
   LEFT OUTER JOIN (
         SELECT 214 AS filterValue_val UNION
         SELECT 215 UNION
         SELECT 216 UNION
         SELECT 217 ) AS tbl 
      ON tbl_IN_VS_AND.filterCriterion_sv = tbl.filterValue_val
   WHERE tbl.filterValue_val IS NULL

It definitely feels odd, placing things you would normally put in a WHERE clause into a derived table then looking for absent values, but the performance benefit gives us a compelling reason to consider doing this. On this test run of 100 executions, this odd query was consistently outperforming the quicker of our original two queries by about 19%:

Not In v Not Equal - SQL Server Central