SQL IN: A Beginner’s Guide

The SQL IN keyword lets you select values that appear in a range of values. You can specify a query inside an IN statement to search for records whose column has a value in the result of a query. The IN keyword appears after a WHERE statement.

You can retrieve records whose values are equal to one of multiple possible values from a database using OR conditions. If you’re looking to compare a column to a long list of values, specifying multiple OR conditions can make your query messy.

There’s another option: the SQL IN operator. In this guide, we’re going to talk about what the SQL IN operator is and how it works. We’ll walk through a few examples of this operator in action so that you can learn how to use it.

What is the SQL IN Operator?

The SQL IN operator checks whether a value exists in a set of values. The set of values can either be comma-separated or a query. If you specify a query, that query will execute. Its results will be compared with the column you have stated in the WHERE statement.

The syntax for the SQL IN operator is:

SELECT column1, column2 FROM table
WHERE column1 IN (value1, value2);

The IN operator accepts a list of values to which you want to compare to the value in a column separated by commas. You must surround the values in curly brackets. This applies even if you are using a SELECT statement in the IN clause.

You can specify as many values in an IN statement as you want. This makes them more convenient than using SQL OR statements if you want to compare a value to multiple values.

How to Use the SQL IN Operator

We’ve got a database called “employees”. This database stores information on all the people who work for a pharmaceutical sales business. Our database looks like this:

Let’s retrieve a list of all the employees who work in either Los Angeles or San Francisco. We are going to select the names of each employee, their titles, and the name of the branch at which they are employed.

The following SQL SELECT statement will retrieve these values from our database:

SELECT name, title, branch FROM employees
WHERE branch IN ('Los Angeles', 'San Francisco');

Our statement returns:

Only employees working in the San Francisco or Los Angeles branches are returned.

A Look at Multiple SQL OR Statements

Our above query that retrieves all employees at the San Francisco or Los Angeles branches could have been written using an OR operator:

SELECT name, title, branch FROM employees
WHERE branch = 'Los Angeles' OR branch = 'San Francisco';

This statement is syntactically correct. If you’re looking to compare a value in a database to a long list of values, OR statements can become cluttered. Imagine searching through four values. You would need to specify three OR statements in your query.

For instance, suppose we wanted to retrieve a list of all employees who work at the Cambridge, San Francisco, Los Angeles, and Denver branches. Our query would look like this:

SELECT name, title, branch FROM employees
WHERE branch = 'Los Angeles' OR branch = 'San Francisco' OR branch ='Denver' OR branch = 'Cambridge';

Using an IN statement would make this query easier to read.

SQL IN Using SELECT

The IN operator accepts the result of an SQL SELECT query.

This means that you can compare values in a database to the result of another query. To demonstrate this, we’re going to create a new table: probation.

Let’s retrieve a list of employees who are currently on probation. Consider the following query:

SELECT name FROM employees
WHERE id IN (SELECT employee FROM probation);

This query will select all employees whose employee ID appears in the “probation” table.

Our query returns:

The query “SELECT employee FROM probation” selects all the IDs of employees who are on probation. The result of this query (1, 3, 6), is then used with the IN operator.

Our main query returns a list of all employees whose value is equal to 1, 3, or 6.

Conclusion

The SQL IN operator verifies if a column is equal to one of many values. You can specify the values to which the column should compare using a comma-separated list. Or, you can specify a query.

As a challenge, write a query that would retrieve the names of all employees whose title is either:

• Sales Director or;
• Senior Sales Representative.

The name of the table you should query is “database.” Compare the query you wrote with the ones above to see if you are right.

We’ve written a comprehensive guide on how to learn SQL, featuring a list of top learning resources and online courses. If you’re interested in reading the guide, visit our How to Learn SQL page.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match