Examples of the IN Operator in SQL with HR Schema
The IN operator in SQL is a powerful tool that allows you to check if a value matches any value in a specified list. It's particularly useful when you need to filter rows based on multiple possible values for a single column.
Basic Syntax
|
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...); |
Practical Examples Using the HR Schema
Example 1: Find employees with specific job roles
|
1 2 3 |
SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROG', 'SA_REP', 'FI_ACCOUNT'); |
This query retrieves the last names and job IDs of all employees who work as IT Programmers, Sales Representatives, or Financial Accountants.
Example 2: Find employees from multiple departments
|
1 2 3 |
SELECT first_name, last_name, department_id FROM employees WHERE department_id IN (30, 60, 90); |
This query returns employees who work in department 30 (Purchasing), 60 (IT), or 90 (Executive).
Example 3: Find countries in specific regions
|
1 2 3 |
SELECT country_name, region_id FROM countries WHERE region_id IN (1, 3); |
This query returns countries located in regions 1 (Europe) and 3 (Asia).
Example 4: Find managers with specific employee IDs
|
1 2 3 |
SELECT department_name, manager_id FROM departments WHERE manager_id IN (100, 101, 108); |
This query returns departments managed by employees with IDs 100, 101, or 108.
Example 5: Find employees hired in specific years
|
1 2 3 |
SELECT first_name, last_name, hire_date FROM employees WHERE TO_CHAR(hire_date, 'YYYY') IN ('2016', '2017'); |
This query returns employees hired in either 2016 or 2017.
Example 6: Find locations in specific countries
|
1 2 3 |
SELECT city, state_province FROM locations WHERE country_id IN ('US', 'UK', 'CA'); |
This query returns cities and states/provinces located in the United States, United Kingdom, or Canada.
Example 7: Using IN with a subquery
|
1 2 3 4 5 6 7 |
SELECT first_name, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE department_name LIKE '%IT%' OR department_name LIKE '%Sales%' ); |
This query finds employees who work in any department that has “IT” or “Sales” in its name.
Example 8: Combining IN with other conditions
|
1 2 3 4 |
SELECT first_name, last_name, salary FROM employees WHERE job_id IN ('SA_REP', 'SA_MAN') AND salary > 10000; |
This query finds sales representatives or sales managers who earn more than $10,000.
Example 9: Using NOT IN
|
1 2 3 |
SELECT department_name FROM departments WHERE department_id NOT IN (10, 20, 30); |
This query returns departments except for those with IDs 10, 20, and 30.
Example 10: Using IN with multiple columns (composite IN)
|
1 2 3 |
SELECT first_name, last_name FROM employees WHERE (department_id, manager_id) IN ((90, 100), (60, 103)); |
This query returns employees who either work in department 90 with manager 100, or department 60 with manager 103.
Key Benefits of the IN Operator
-
- Improved readability – More concise than multiple OR conditions
- Better performance – Often optimized better than equivalent OR statements
- Flexibility – Works with literals, variables, or subqueries
- Simplicity – Easy to add or remove values from the filter list
The IN operator is especially valuable when filtering based on lookup values or when the filter criteria come from another query.
