SQL IN Syntax
SELECT column1, column2
FROM table_name
WHERE column IN (value1, value2, value3, ...);We will use this Employees table:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+IN with Text Values
SELECT Name, Department
FROM Employees
WHERE Department IN ('IT', 'HR', 'Sales');+-----------+------------+ | Name | Department | +-----------+------------+ | Priya K. | Sales | | Arun M. | IT | | Emma W. | Sales | | Ravi S. | IT | | Sara L. | HR | +-----------+------------+
IN vs Multiple OR
These two queries produce the exact same result, but IN is shorter and easier to read:
SELECT Name, City FROM Employees
WHERE City = 'Mumbai'
OR City = 'Delhi'
OR City = 'London';SELECT Name, City FROM Employees
WHERE City IN ('Mumbai', 'Delhi', 'London');+-----------+--------+ | Name | City | +-----------+--------+ | Priya K. | Mumbai | | John D. | London | | Arun M. | Delhi | | Ravi S. | Mumbai | +-----------+--------+
IN with Numeric Values
SELECT Name, Salary FROM Employees
WHERE Salary IN (55000, 62000, 78000);+-----------+--------+ | Name | Salary | +-----------+--------+ | Priya K. | 55000 | | John D. | 62000 | | Arun M. | 78000 | +-----------+--------+
NOT IN — Exclude Values
SELECT Name, City FROM Employees
WHERE City NOT IN ('Mumbai', 'London');+-----------+-----------+ | Name | City | +-----------+-----------+ | Arun M. | Delhi | | Emma W. | Sydney | | Sara L. | New York | +-----------+-----------+
⚠️ NOT IN and NULL
Be careful with NOT IN when the list contains NULL. NOT IN (value1, NULL) returns no rows because any comparison with NULL returns UNKNOWN. Always ensure your IN list does not contain NULL values when using NOT IN.
IN with a Subquery
You can use IN with a subquery instead of a static list. The subquery returns a list of values dynamically:
SELECT Name, Department
FROM Employees
WHERE Department IN (
SELECT Department FROM Employees
WHERE Salary > 70000
);This finds employees in departments that have at least one person earning over 70,000. The subquery returns ‘IT’, so all IT employees are shown.
For a complete reference of comparison operators, see the official MySQL comparison operators documentation. In the next chapter, you will learn SQL BETWEEN to filter by a range of values.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that finds employees who work in Mumbai, Sydney, or New York.
SELECT Name, City, Department
FROM Employees
WHERE City IN ('Mumbai', 'Sydney', 'New York');Expected Output:
+-----------+-----------+------------+ | Name | City | Department | +-----------+-----------+------------+ | Priya K. | Mumbai | Sales | | Emma W. | Sydney | Sales | | Ravi S. | Mumbai | IT | | Sara L. | New York | HR | +-----------+-----------+------------+
📝 What You Learned in This Chapter
INfilters rows matching any value in a list- IN is a cleaner alternative to multiple OR conditions
- Works with text values, numbers, and subqueries
NOT INexcludes rows matching values in the list- Be careful with NOT IN when the list might contain NULL
Frequently Asked Questions
IN operator checks if a value matches any value in a list. It is shorthand for multiple OR conditions. WHERE City IN ('Mumbai', 'Delhi') is the same as WHERE City = 'Mumbai' OR City = 'Delhi'.WHERE column IN (SELECT column FROM other_table) filters rows based on values returned by a subquery. This is powerful for dynamic filtering where the list of values comes from another table or calculation.NOT IN returns no rows if the list contains NULL. This is because value <> NULL evaluates to UNKNOWN, which makes the entire NOT IN condition UNKNOWN. Always filter out NULLs from subqueries used with NOT IN.📖 Chapter 17 of 30 — SQL Tutorial on Neotech Navigators
