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

How to INNER JOIN on the same table twice in SQL statement

Having more than one INNER JOIN to the same table can be confusing.

More than one INNER JOIN can create a messy SQL statement.

If you don’t use aliases they can become difficult to read.

So join them with aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

Table users is aliased as u, products as p.

Then you can prepend the alias to SELECT a field from a table.

p.id is equivalent of writing products.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you use the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe below and you’ll be sure to receive them!

Or follow me on Twitter

How to join the same table twice in a single SQL query statement?

Having more than one join to the same table can be confusing.

You might not have even thought it was possible to have multiple joins to the same table.

It is possible and easy to do with the help of aliases.

Aliases for table names

Aliases allow you to reference the table name with an abbreviation.

For example if you have a table called “products” then you reference that table by creating an alias of “p”.

With MySQL you easily create an alias like this:

SELECT p.id FROM products p

Multiple joins to the same table can quickly create a messy SQL statement. If you don’t use aliases they can become difficult to read.

Alias SQL example:

If your database tables need to have two or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that with a simple SQL query like this.

In this example, we use the aliases p for the products table and u for the users table:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

If you want to find out who modified the product you can do that with a similar query using the same aliases:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you reference the same table.

In this example, we use the aliases u1 for the first reference and u2 for the second reference:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice in single efficient query.

Want all the SQL statements from above and the CREATE table statements?

Grab the code and try it for yourself

New to MySQL, read this blog post… MySQL terminology guide

Found this helpful?

Signup below, for more helpful tips.