SQL IN Operator
The IN
operator in SQL allows you to test whether a value matches any value within a list of values. It's a more concise and often more efficient way to write multiple OR
conditions.
IN Operator: Definition and Usage
The IN
operator simplifies queries by letting you check if a column value exists within a set of specified values. This makes your SQL code more readable and often performs better than writing out multiple `OR` conditions.
Syntax
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
Example Database
Let's use this sample 'Customers' table for the examples:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Examples
Using IN with a List of Values
This query selects customers from Germany, France, or the UK.
Syntax
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
Output
(Rows where Country is 'Germany', 'France', or 'UK' will be returned)
Using NOT IN
This query selects customers who are NOT from Germany, France, or the UK.
Syntax
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
Output
(Rows where Country is NOT 'Germany', 'France', or 'UK' will be returned)
Using IN with a Subquery
This query finds customers who have placed at least one order (assuming an 'Orders' table with a 'CustomerID' column).
Syntax
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);
Output
(Customers with at least one order will be returned)
Using NOT IN with a Subquery
This query finds customers who have *not* placed any orders.
Syntax
SELECT * FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);
Output
(Customers with no orders will be returned)