The IN Statement: SQL Fundamentals

Howdy, budding SQL masters! It’s been a minute since the last SQL post! I’ve taken some time to devote to Alexa Skills, some other personal housekeeping, and adopting a dog with my wife!!

Last time we talked SQL, we learned the BETWEEN statement to hone in on the exact values we wanted for a SQL query. Prior to that, we looked at the LIMIT statement to get a small return data sample and also working with ORDER BY to take sorting and filtering into our own hands.

Today we’ll be working with the IN statement.

About the IN Statement

The IN SQL operator is a companion to the WHERE clause to see if a value matches any value in a list of values. Sounds something kind of like VLOOKUP’s, yeah? The syntax of an IN statement might look like:

value IN (value1, value2)

The expression in your SQL query will return true if the value(s) you’ve specified in the query match any value in the list you’re referencing.

Sub-queries: Using the IN Statement with SELECT

The list of values is not limited to a static number or list of strings. That is, you can be a bit more dynamic and free-flowing with your query by getting the value via a SELECT statement.

This is also known as a sub-query. To illustrate, the syntax might look like:

value IN (SELECT value FROM table)

NOT IN: Not Making Fetch Happen

The IN statement has a similar corollary as the BETWEEN statement- a NOT modifier. So, we can take the inverse of a statement. (Drawing on some experience in data analysis, this can just as, if not more useful than the original statement!)

value NOT IN (SELECT value FROM table)

Sample SQL Queries for the NOT IN Statement

Alright, let’s run some sample queries using the IN statement! Here’s our first go, working again with the ever-present dvdrental sample database. Below, we are selecting rental information for customers matching only a certain ID cohort (think perhaps loyalty group?), with a few columns and sorting by descending order for the return date:

SELECT customer_id,rental_id,return_date

FROM rental

WHERE customer_id IN (1,2)

ORDER BY return_date DESC;

Below, we see our results returned as intended!

How about our corollary? If we want to add the NOT IN modifier, it’s as straightforward as you would imagine.

FROM rental

WHERE customer_id NOT IN (1,2)

ORDER BY return_date DESC;

Above, we again receive the desired result. I believe we also get a lesson in the importance of clean data here, as blank values are presented first. Let’s try one more sample query, just for kicks. We’ll switch it up ever so slightly, now working with the payment table.

SELECT city_id,city,last_update

FROM city

WHERE country_id IN (44,82,60)

ORDER BY city ASC;

Above, we’ve demonstrated that we don’t necessarily need to display a column we use in the query (country_id) and have sorted by city name in ascending order.

Wrap Up

So why the IN statement? At face value, it seems rather simplistic among SQL clauses we’ve explored thus far. Let’s consider the following.

The IN statement allows us to avoid a messy swath of values courtesy of BETWEEN (if we’re working with a proper large data set), or daisy chain list of a bunch of equals OR statements. Here’s another plus, it’s thought by some that pgAdmin / PostgreSQL will execute the IN query faster than the list of OR statements.

Alright, that’s it for today’s article. If you’re like me and need a quick refresh, revisit our page on Learning SQL that contains all the topics we’ve covered so far.

Zach