sql_in

SQL IN Operator

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. It serves as a shorthand for multiple OR conditions, often making queries more readable and sometimes more performant.

SQL IN Syntax

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

The IN operator can also be used with a subquery that returns a single column of values:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT another_column FROM another_table WHERE condition);

You can also use NOT IN to exclude rows that match any value in the list.

Demo Database: Northwind

We’ll use the “Customers” table from the Northwind sample database.

CustomerID | CustomerName                      | City       | Country
-----------|-----------------------------------|------------|---------
1          | Alfreds Futterkiste             | Berlin     | Germany
2          | Ana Trujillo Emparedados y helados| México D.F.| Mexico
3          | Antonio Moreno Taquería         | México D.F.| Mexico
4          | Around the Horn                   | London     | UK
5          | Berglunds snabbköp              | Luleå      | Sweden
...

IN Operator Examples

Example 1: Selecting customers from specific cities

The following SQL statement selects all customers located in ‘Paris’ or ‘London’:

SELECT *
FROM Customers
WHERE City IN ('Paris', 'London');

This is equivalent to:

SELECT *
FROM Customers
WHERE City = 'Paris' OR City = 'London';

Example 2: Using IN with a Subquery

Suppose you want to find all customers who have placed orders. (Note: An INNER JOIN is often a more direct way to do this, but this illustrates IN with a subquery).

SELECT CustomerName, City, Country
FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

Practical Application:

The IN operator is very useful when:

• Filtering by a fixed set of known values (e.g., product IDs, status codes, specific regions).
• Filtering based on results from another query (dynamic criteria).
• Improving query readability compared to long chains of OR conditions.

Considerations:

• Performance: For very long lists of literal values, performance might vary by database system. Some systems optimize IN well, while others might struggle with extremely large lists. Using IN with a subquery is generally well-optimized.
• NULL values: Be cautious when NULL values are present in the list or the column being compared. column_name IN (value1, NULL, value2) will behave differently from what might be expected regarding NULL comparisons. Typically, NULL does not equal NULL and NULL does not equal any other value.