IN Operator

Definition

The IN operator in SQL is used to check whether a value matches any value in a list of values. It is typically used in the WHERE clause of a SELECT, UPDATE, or DELETE statement.

Examples

If we wanted to find all the customers that are in either New York or Los Angeles, we could use the OR operator:

SELECT *
FROM customers
WHERE city = 'New York'
OR city = 'Los Angeles'

Now, if we wanted to also add another city to the list, as you can predict, we'd have to add another OR statement that would cover that city. If we were to increase the number of filtered cities to 10, this would quickly become a pretty long and unreadable query.

This is where SQL's IN operator comes into play. It solves that problem by allowing us to provide a list of values that we want a column to be checked against.

As an example of using the IN operator, filtering the customers by ones for which the city column is either "New York" or "Los Angeles" becomes:

SELECT * 
FROM customers 
WHERE city 
IN ('New York', 'Los Angeles');

You can also use the IN operator with subqueries. For example, you can use it to select all customers who live in a city that appears in another table.

SELECT * 
FROM customers 
WHERE city 
IN (SELECT name FROM cities);

It's also possible to negate the IN operator to check whether a value does not match any value in a list by using NOT IN. For example:

SELECT * 
FROM customers 
WHERE city 
NOT IN ('New York', 'Los Angeles');

This query will select all rows from the customers table where the value of the city column is neither "New York" nor "Los Angeles".

Best Practices

Here are some best practices for using the SQL IN operator:

• Use the IN operator instead of multiple OR operators: When filtering results based on multiple values for the same column, it's more efficient and readable to use the IN operator rather than multiple OR operators. For example, instead of writing:

SELECT column1, column2 
FROM table_name 
WHERE column_name = 'value1' OR column_name = 'value2';

You should use:

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

• Use the NOT IN operator for negation: To exclude results that match certain values in a list, you can use the NOT IN operator. For example, to select all rows from a table where the value of the column 'column_name' is not in a list of values:

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

• Be careful with NULL values: The IN operator does not return any rows that contain a NULL value in the column being tested. If your table contains NULL values and you want them to be included in the results, you can use both conditions by joining them with the OR operator: ... WHERE ( (column_name IN (1, 2, 3)) OR (column_name IS NULL) )

• Indexed columns perform better: The IN operator generally performs better on indexed columns, so when possible, use the IN operator on indexed columns.

• Use a subquery if the IN list is too large: You should keep the list passed to the IN operator as short as possible. If the list becomes very long it can negatively impact query performance and also become hard to read. Instead of passing a long list to the IN operator, you can use a subquery to create a temporary table with the values you want to match, and then join that table with the main table using the IN operator.