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
If we wanted to find all the customers that are in either New York or Los Angeles, we could use the
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".
Here are some best practices for using the SQL
- Use the
INoperator instead of multiple
ORoperators: When filtering results based on multiple values for the same column, it's more efficient and readable to use the
INoperator rather than multiple
ORoperators. 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 INoperator for negation: To exclude results that match certain values in a list, you can use the
NOT INoperator. 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
INoperator does not return any rows that contain a
NULLvalue in the column being tested. If your table contains
NULLvalues and you want them to be included in the results, you can use both conditions by joining them with the
... WHERE ( (column_name IN (1, 2, 3)) OR (column_name IS NULL) )
Indexed columns perform better: The
INoperator generally performs better on indexed columns, so when possible, use the
INoperator on indexed columns.
Use a subquery if the
INlist is too large: You should keep the list passed to the
INoperator 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
INoperator, 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