SQL WHERE IN – Explained With Examples

Are you tired of manually filtering data in your database and looking for a more efficient way to filter data with multiple values? SQL WHERE IN might be the solution you need!

If you’re working with SQL databases, you’ve probably come across the WHERE IN operator. This operator is used to specify multiple values in a WHERE clause, allowing you to filter your data more precisely.

With this operator, you can specify a list of values to match against a column in your table. This list can be hardcoded or generated by a subquery, giving you a lot of flexibility in how you filter your data.

For example, you could use the IN operator to find all customers from a specific list of cities or to filter results based on a set of product IDs.

One of the benefits of using the WHERE IN operator is that it can make your queries more efficient. By using a list of values instead of multiple OR conditions, you can reduce the amount of processing that needs to be done by the database.

This can be especially important when working with large datasets or complex queries, where even small optimizations can make a big difference in performance.

What Is SQL WHERE IN Clause?

The SQL WHERE IN clause is used to filter data based on a list of values. It allows you to specify multiple values in a WHERE clause using a shorthand for multiple OR conditions. The syntax for the WHERE IN clause for SQL Server, PostgreSQL, MYSQL and Oracle is as follows:

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

If we consider the above SELECT statement, the WHERE IN clause allows you to retrieve data from a table where the specified column matches any of the values in the list. It is a useful syntax for filtering data when you have a specific set of values to search for.

How Does SQL WHERE IN Clause Work?

The clause works by comparing the specified column to each value in the list. If the column matches any of the values in the list, the row is included in the result set.

Let’s say you have a table named “customers” that consists of three columns: “customer_id”, “customer_name”, and “country”. You want to retrieve all the customers from the USA, Canada, and Mexico. The following query will show you how the WHERE IN clause is used: SELECT customer_id, customer_name, country FROM customers WHERE country IN (‘USA’,’Canada’,’Mexico’);

Examples Of SQL WHERE IN Clause

If you want to filter results based on a list of possible values, you can use the SQL WHERE IN clause. This clause provides an easy way to select rows where a certain column matches one of a set of values.

Here are five examples of how the clause can be used:

1. Using SQL WHERE IN Clause with a Single Value

Let’s say you want to select all the rows in a table where the status column is set to “published.” You can do this using the following SQL statement:

SELECT * 
FROM my_table 
WHERE status IN ('published');

This will return all the rows where the status column is set to “published.”

1. Using SQL WHERE IN Clause with Multiple Values

Now let’s say you want to select all the rows where the status column is set to either “published” or “draft.” You can use the following SQL statement:

SELECT * 
FROM my_table 
WHERE status IN ('published', 'draft');

This will return all the rows where the status column is set to “published” or “draft.” Also, you can add as many values as you want, separated by commas.

1. Using SQL WHERE IN Clause with Delete Statement

Suppose you have a table called “customers” with the following data:

To delete rows from this table based on multiple values in the “City” column, the SQL WHERE IN clause can be used with a DELETE statement, as shown in the following query:

DELETE FROM customers
WHERE City IN ('New York', 'Chicago', 'Miami');

This statement will delete all rows from the “customers” table where the “City” column names match any of the values in the list, i.e. ‘New York’, ‘Chicago’, ‘Miami’. In the above example, it will delete the first, third, and fifth rows from the table.

Note that when using the WHERE IN clause with a DELETE statement, you should be very careful to avoid mistakes by ensuring that you are deleting the correct data. It’s best to test your query on a small subset of data before running it on your entire database.

1. Using the WHERE IN operator and SQL WHERE clause to filter data based on numeric values

SELECT *
FROM customers
WHERE age IN (25, 30, 35)
AND city = 'London';

This query will return all rows from the “customers” table where the age column contains the numeric values 25, 30, or 35, and the city column contains the value “London”.

Note that the WHERE IN operator is a logical operator that only allows you to specify multiple values for a single column. The SELECT statement is used to retrieve data from a table, and the WHERE clause is used to filter the data based on specific conditions.

In this example, the query returns rows from the “customers” table where the age column matches one of the specified numeric values and the city column matches the value “London”. The column names used in this query are “age” and “city,” and the table being queried is “customers”.

1. Using WHERE IN with the SELECT statement and the IN operator.

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE country = 'USA'
);

In this example, we’re using a subquery to retrieve all customer IDs from the “customers” table where the country column contains the value “USA”. We’re then using the WHERE IN operator to filter the “orders” table based on those customer IDs.

This query will return all rows from the orders table where the customer_id column matches one of the customer IDs returned by the subquery.

Remember, the WHERE IN operator is a comparison operator that allows you to compare a column to a list of values. In this case, we’re comparing the customer_id column to a list of customer IDs returned by the subquery.

By using this operator and a subquery, you can filter data based on specific criteria and retrieve only the data that meets your needs.

2 Major Benefits of Using SQL WHERE IN Clause?

1. Improved Query Performance

The clause offers one of the biggest benefits in terms of query performance. When filtering data based on multiple values, using the clause can be much more efficient and faster than writing multiple WHERE clauses with OR operators. This is because the database engine can optimize the query execution plan to efficiently retrieve the necessary data.

For example, consider the following SQL queries with the “customers” table, where we’re trying to identify the customer details based on their countries.

SELECT CustomerID,CustomerName,phone,Email,CustomerCounter
FROM customers WHERE country='USA' 
			OR country='Canada' 
			OR country='Mexico' ;

 SELECT CustomerID,CustomerName,phone,Email,CustomerCounter
FROM customers 
WHERE country IN ('USA', 'Canada', 'Mexico'); 

As seen in the above example, using the clause can result in a significant improvement in query performance and save you more time. It’s easier to select all countries at once rather than to select country after country using the OR operator in a much larger dataset.

1. Simplified Query Writing

Another major benefit of using the SQL WHERE IN clause is that it can simplify the process of writing complex queries. When you need to filter data based on multiple values, using multiple WHERE clauses with OR operators can quickly become cumbersome and difficult to manage.

Using the clause allows you to specify all the values you want to filter in a single statement, making your queries easier to read and maintain. This can be especially useful when working with large datasets or when you need to write complex queries with multiple conditions.

For example, consider the following SQL query:

SELECT *
FROM products 
WHERE category='Electronics' AND (brand='Apple' OR brand='Samsung' OR brand='Sony') 

SELECT *
FROM products 
WHERE category='Electronics' AND brand IN ('Apple', 'Samsung', 'Sony') 

In both the queries from the above example, the query returns all “electronics” products from Apple, Samsung, and Sony.

However, using the WHERE IN clause as seen in the latter query, can simplify the process of using complex queries and make them easier to read and understand.

Furthermore, It also helps avoid errors and makes it less time-consuming.

Our Final Word

Congratulations! You are now aware of how to use the SQL WHERE IN clause to filter data based on a list of values. Let us evaluate what we have learnt:

By using WHERE IN, you can simplify a range of SQL queries and create them to retrieve a more efficiently written output.

Remember, WHERE IN is shorthand for multiple OR conditions, so it can replace long lists of OR statements. Additionally, the clause can be used with subqueries, allowing you to filter data based on the results of another query.

When using WHERE IN, it is important to consider the data types of the values you are comparing. If the data types do not match, you may need to use type conversion functions to ensure an accurate comparison.

Overall, the SQL WHERE IN clause is a powerful yet basic syntax for filtering data in SQL queries. By mastering this clause, you can improve the performance and readability of your SQL code, making it easier to work with and maintain in the long run.