When to use the IN operator in your SQL WHERE statement

Finding your SQL statements growing and difficult to understand?

Inherited some code that contains complex looking SQL statements?

Let me show you one way to make them easier to read and understand…

Sooner or later you will have to create a SQL statement with multiple WHERE conditions, usually AND and OR’s.

If you have three or more conditions your SQL can easily become difficult to read and difficult to understand the logic.

Your brain (and mine) can start to hurt as you wonder…

Are all those WHERE conditions returning the expected results?

Database table cars_for_sale

id for_sale dealer make model engine price description
1 1 Crumps cars Audi S4 V8 $9999 Lorem Ipsum…
2 1 Crumps cars Audi A4 V6 $9999 Lorem Ipsum…
3 1 Crumps cars Audi Q7 V10 $9999 Lorem Ipsum…
4 0 Crumps cars Audi A4 4cyl $9999 Lorem Ipsum…
5 1 Crumps cars Audi A4 4cyl $9999 Lorem Ipsum…
6 1 Another dealer Audi S4 V8 $9999 Lorem Ipsum…
7 1 Bobs cars Audi A4 V6 $9999 Lorem Ipsum…
8 0 Crumps cars Audi Q7 V10 $9999 Lorem Ipsum…
9 0 Crumps cars Audi A6 V8 $9999 Lorem Ipsum…
10 1 Crumps cars Audi A8 V8 $9999 Lorem Ipsum…

Consider the following SQL query a reader recently sent to me.

(Not the actual query, but modified here by me for privacy for this example)

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND (engine != 'V12' OR engine != 'V10' OR engine != 'V8' OR engine != 'V6')
ORDER BY make, model

The reader wants all cars for sale at ‘Crumps cars’ dealership but don’t have a certain engine type, V12, V10, V8, V6.

Immediately I find the multiple conditions difficult to read and understand if the conditions are going to work together.

If there were more OR’s than above it’s only going to get worse…

What if you could save the brain ache and make your WHERE statement easier to write and read!

Well there is a simple way in my my mind and that’s using the IN comparison operator in your WHERE clause.

Let’s use it to rescue this SQL statement and rewrite it like this:

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine NOT IN ('V12','V10','V8','V6')
ORDER BY make, model

Now you can simply have a comma seperated list of multiple values.
Each of which we don’t want the engine column to match with, by using the NOT IN operator.

Alternatively if you do want them to match just remove the NOT

Then every result will have a “V” engine from the list

SELECT id, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine IN ('V12','V10','V8','V6')
ORDER BY make, model