You can retrieve records whose values are equal to one of multiple possible values from a database using OR conditions. The trouble with this approach is that 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 IN operator checks whether a value exists in a set of values.
The syntax for this 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. These values must be surrounded by curly brackets.
You can specify as many values in an IN statement as you want. This makes them more convenient than using 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:
|1||Thomas Carlton||Sales Representative||Cambridge||29400|
|2||Lisa Nelson||Senior Sales Representative||San Francisco||32000|
|3||Victoria Carlisle||Sales Director||Cambridge||37800|
|4||Julie Forsythe||Senior Sales Representative||San Francisco||32000|
|5||Hallie Peters||Sales Representative||Los Angeles||29400|
|6||Rachel Parsons||Sales Representative||San Francisco||29400|
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 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:
|Lisa Nelson||Senior Sales Representative||San Francisco|
|Julie Forsythe||Senior Sales Representative||San Francisco|
|Hallie Peters||Sales Representative||Los Angeles|
|Rachel Parsons||Sales Representative||San Francisco|
Only employees working in the San Francisco or Los Angeles branches are returned. This query could have been written using an OR operator:
SELECT name, title, branch FROM employees WHERE branch = 'Los Angeles' OR branch = 'San Francisco';
The drawback with using an OR statement is that if you’re looking to compare a value in a database to a long list of values, OR statements can become cluttered.
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 a 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.
The SQL IN operator checks whether the value of a column is in a list of values. This operator is used in place of an OR operator when you want to compare the value of a column to a long set of values.
Now you’re ready to use the SQL IN operator like a database expert!