SQL IN Operator

Why do you need to know SQL IN? Find out what you can do with this small but powerful operator.

The IN operator is a fundamental tool in SQL, enabling data analysts and developers to filter and retrieve specific subsets of data from databases. While its basic usage may seem straightforward, mastering the nuances and advanced applications of the SQL IN operator can greatly enhance your SQL skills and lead to more powerful and optimized queries.

In this comprehensive guide, we'll dive deep into the SQL IN operator, covering its syntax, use cases, performance considerations, and best practices.

Introduction to the SQL IN Operator

The SQL IN operator is used to check if a value matches any value in a specified list or subquery. It provides a concise way to combine multiple conditions using OR logic without having to explicitly write out each condition separately. This can greatly simplify your SQL queries and improve their readability, especially when you’re dealing with a large number of conditions.

If you are looking for a starting point to build solid SQL foundations, I recommend our SQL Basics course. It contains 129 hands-on exercises so you can master fundamental SQL and build your confidence. If you already know the basics of SQL, check out our SQL Practice track – it has over 1,000 exercises to help you sharpen your skills.

Using the SQL IN Operator

One of the most common use cases for using SQL IN is to check if a value matches any value in a list. Here's an example:

SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

In this query, the SQL IN operator is used to retrieve all rows from the employees table where the department_id is 10, 20, or 30.

IN Operator Syntax

The SQL IN operator acts as a shorthand for multiple OR conditions on the same field, making the query more concise and readable. Here’s the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Using the SQL NOT IN Operator

The SQL NOT IN operator is the opposite of the SQL IN operator. It is used to retrieve records where the value does not match any value in the specified list. Here's an example:

SELECT *
FROM products
WHERE product_category NOT IN ('Electronics', 'Clothing');

This query retrieves all rows from the products table where the product_category is not 'Electronics' or 'Clothing'.

Using IN with SELECT

The SQL IN operator can also be used in conjunction with a subquery, which is a query nested inside another query. This powerful combination allows you to filter data based on the results of another query. Here's an example:

SELECT product_name, product_price
FROM products
WHERE product_id IN (
SELECT product_id FROM orders WHERE order_date >= '2023-01-01'
);

In this query, the subquery SELECT product_id FROM orders WHERE order_date >= '2023-01-01' retrieves a list of product_id values for orders placed on or after January 1, 2023. The main query then uses the IN operator to retrieve the product_name and product_price for all products whose product_id is in the list returned by the subquery.

This is just one example of how you can utilize a subquery in an IN operator. There are numerous types of subqueries you can write in SQL, but for a better understanding we recommend having a look at more examples of how to use a subquery.

Using NOT IN with SELECT

As with the previous example, you can use the NOT IN operator with a subquery to retrieve records that do not match the results of the subquery. Here's an example:

SELECT employee_name, department_name
FROM employees
WHERE department_id NOT IN (SELECT department_id
 FROM departments
 WHERE location = 'New York');

This query retrieves the employee_name and department_name for all employees who are not in departments located in New York. The subquery SELECT department_id FROM departments WHERE location = 'New York' retrieves a list of department_id values for departments located in New York. The main query then uses the SQL NOT IN operator to exclude employees whose department_id is in the list returned by the subquery.

Exercise: IN Operator Challenge

Now that we’ve read about different ways the IN operator can be used to solve complex SQL problems, it’s time to practice what we’ve learned.

Imagine you are working with a database that stores information about books. The database has a table named Books with the following columns: BookID, Title, AuthorID, and GenreID. There's another table named Genres with the columns GenreID and GenreName. Here is an example of a few rows these tables would contain.

The Books table:

The Genres table:

Your challenge is to write a SQL query that selects all titles of books that belong to the 'Science Fiction', 'Fantasy', or 'Mystery' genres. (Assume you do not already know the GenreID values for these genres.)

Solution:

SELECT Title
FROM Books
WHERE GenreID IN (SELECT GenreID 
  FROM Genres 
  WHERE GenreName = 'Science Fiction' 
    OR GenreName = 'Fantasy' 
    OR GenreName = 'Mystery');

Explanation:

Select book titles from the Books table. Filter the results using a WHERE clause with the IN operator to include only rows where the GenreID is the same as GenreID in the Genres table for either 'Science Fiction', 'Fantasy', or 'Mystery'.

Alternatively, you could write a query that uses two IN operators:

SELECT Title
FROM Books
WHERE GenreID IN (SELECT GenreID 
  FROM Genres 
  WHERE GenreName 
  IN ('Science Fiction','Fantasy','Mystery'));

Result:

Advanced IN Examples and Use Cases

While the basic usage of the SQL IN operator is relatively straightforward, its true power lies in its ability to handle complex scenarios and advanced use cases. Let's explore some of these:

Correlated Subqueries

Correlated subqueries are subqueries that reference columns from the outer query. This allows you to filter data based on complex criteria that involve multiple tables and conditions. Here's an example:

SELECT product_name, category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.product_id IN (SELECT product_id
  FROM order_items oi
  WHERE oi.order_id IN (SELECT order_id
FROM orders
WHERE customer_id = 1001));

In this query, the innermost subquery retrieves the order_id values for a specific customer. The middle subquery then uses those order_id values to retrieve the corresponding product_id values from the order_items table. Finally, the main query uses the SQL IN operator to retrieve the product_name and category_name for those products.

Correlated subqueries are just one of the advanced ways you can use the IN operator. For getting a deep understanding of all types of subqueries we recommend these 15 subquery practice exercises.

Handling Large Lists of Values

When working with large lists of values, using a subquery or a temporary table may be more efficient than listing all values in the SQL IN clause. This can improve query performance and reduce the risk of hitting any query length limits imposed by your database management system. Here's an example:

-- Create a temporary table with the desired values
CREATE TEMPORARY TABLE temp_values (value INT);
INSERT INTO temp_values (value) VALUES (10), (20), (30), ...;
-- Use the temporary table with the SQL IN operator
SELECT *
FROM employees
WHERE department_id IN (SELECT value FROM temp_values);

In this example, a temporary table temp_values is created and populated with the desired values. The main query then uses a subquery to retrieve those values from the temporary table and filter the employees table accordingly.

Combining IN with Other Operators

The IN operator can be combined with other SQL operators, such as AND and OR, to create more complex filtering conditions. This allows you to handle scenarios where you need to filter data based on multiple criteria involving different operators. Here's an example:

SELECT *
FROM orders
WHERE order_date >= '2023-01-01'
  AND customer_id IN (SELECT customer_id
 FROM customers
 WHERE country = 'USA')
  AND total_amount > 1000;

In this query, IN is used in combination with AND to filter orders based on the order date, customer country, and total amount.

Now It’s Time to Practice Using SQL IN

The IN operator is a powerful and versatile tool that can greatly simplify your SQL data analysis and reporting tasks. By understanding its syntax, advanced usage, performance considerations, and best practices, you can leverage IN to effectively filter and retrieve relevant data from your databases while ensuring optimal query performance and maintainability.

Thank you for reading our article; we hope you enjoyed learning about the powerful features of the SQL IN operator. If you’re just starting out with SQL, check out our interactive SQL Basics course. If you’re a seasoned SQL user who’s looking for hands-on practice, check out our  SQL Practice track. Happy learning!