0

SQL: comparing values IN vs INNER JOIN

IN Vs INNER JOIN

Here we will do a quick benchmark on using IN vs INNER JOIN.

 

This came about when a coworker had to run a one-off SQL statement to see if approximately 3,500 codes (from a supplied text file) were in a massive table.

 

She asked a few colleagues on a best approach and there were two main schools of thought:

  1. the easiest way was to just paste them into one long WHERE…IN statement
  2. load them into a table and perform an INNER JOIN

 

That’s where this Curious Consultant started wondering what the fastest way would be: IN vs INNER JOIN. Hence this fresh reading material. 🙂

 

The Caveats

 

Before everyone leaves comments on how this isn’t an entirely accurate test, this is why the code was used below:

  1. We don’t care about INNER JOINs returning duplicates; we just want to know if a record with the code is in the table
  2. we don’t care about execution plans of each and their differences; we only care about about how long these queries take start to finish.
  3. we don’t care that the INNER JOIN can return other columns

 

The Results Are In!

Here are the results, in milliseconds, as to how long each technique took to execute.

The winner is highlighted in green.

Technique

Time to execute, in milliseconds, over 3 runs:

# Records:

360

720

1: IN statement

17, 16, 16

46, 34, 30

2: Non-Indexed column

0, 2, 0

0, 13, 0

3: Clustered Indexed column

0, 0, 0

0, 0, 0

3: Non-clustered Indexed column

0, 0, 0

0, 0, 0

 

The Way To Go

There doesn’t appear to be any surprises in the results when more than just a few records are used.

When less than 100 records are used (results not displayed), everything ran in zero milliseconds.

When nearing the max size of the varchar content allowed, the time to run isn’t that significant to be noticed by the average user. Sure, if it’s in some sort of automated system that you need the fastest way to go, by all means alter the SQL.

Otherwise, do whatever works best for you. Or if your inner-geek is wanting to come out too, arrange your own test to play around. Be sure to post your results here in the comments below. 🙂

 

The SQL Source Code Used

David Lozinski