SQL IN Operator with Examples

The SQL IN operator is a special comparison operator used to check whether a value matches any value in a specified list or the result set of a subquery.

In simple terms, it answers the question, “Is this value present in the given set of values?”

If the value matches one of the specified values from a specified list or result set, the condition evaluates to TRUE, and SQL includes that row in the final result set.

Practical Example of SQL IN Operator

Imagine you have an Employees table with the following data:

employee_idemployee_namedepartment
101RahulIT
102PriyaHR
103AmitSales
104NehaFinance
105RaviIT

Suppose you want to retrieve all employees who work in either the IT or HR department. You can write the following query:

SELECT *
FROM Employees
WHERE department IN ('IT', 'HR');

Expected Output;

employee_idemployee_namedepartment
101RahulIT
102PriyaHR
105RaviIT

How Does the IN Operator Work?

The easiest way to understand the SQL IN operator is to think of it as a membership check. You can understand the working of SQL IN operator in four simple steps:

  • SQL reads the value of the specified column.
  • The IN operator compares that value with each value in the specified list.
  • If a matching value is found, the condition evaluates to TRUE.
  • SQL returns the corresponding row when the WHERE condition is satisfied.

Consider the following example.

WHERE department IN ('IT', 'HR')

The IN operator checks the value of the department column against the list (‘IT’, ‘HR’). Any row where the department matches either value satisfies the condition and is returned in the output. Conceptually, SQL is checking whether the department is one of these values:

  • IT → Match → TRUE
  • HR → Match → TRUE
  • Sales → Match → FALSE
  • Finance → No match → FALSE

Therefore, employees from IT, and HR are selected from the specified list.

SQL IN as a Shorthand for Multiple OR Conditions

One of the main advantages of the IN operator in SQL is that it can make multiple equality conditions shorter and easier to read. For example, the following query uses multiple OR conditions:

SELECT *
FROM Employees
WHERE department = 'IT'
     OR department = 'HR'
     OR department = 'Sales';

You can write the same condition more concisely using IN operator:

SELECT *
FROM Employees
WHERE department IN ('IT', 'HR', 'Sales');

Both queries express the same basic filtering requirement:

department = 'IT'
OR
department = 'HR'
OR
department = 'Sales'

The IN version is generally easier to read, especially when you need to compare a column against several values.

Important Point

The IN operator is not limited to text values. Depending on the column and data type, you can use it with:

  • Numeric values
  • Character/string values
  • Date or timestamp values
  • Values returned by a subquery

For example:

  1. Numeric values
    • WHERE employee_id IN (101, 103, 105)
  2. String values
    • WHERE department IN (‘IT’, ‘HR’, ‘Sales’)

The exact handling of date and timestamp literals can vary between database systems, so date examples should follow the syntax supported by the specific DBMS being used.

Syntax of IN Operator in SQL

In SQL, we use the IN operator with a WHERE condition to check whether a column or expression matches any value from a specified list or the result set of a subquery.

Basic Syntax

The basic syntax of the SQL IN operator is:

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

Here:

  • SELECT: Specifies the columns you want to retrieve.
  • FROM: Specifies the table containing the data.
  • WHERE: Specifies the filtering condition.
  • column_name: The specific column whose value you want to check.
  • IN: The operator that checks whether the column value matches any value inside the parentheses.
  • value1, value2, value3, ...: The list of literal values against which SQL performs the comparison.

Basic Example of the SQL IN Operator with String Values

Let’s start with a simple example to understand how the SQL IN operator filters rows based on multiple possible values.

Example 1: Filtering Departments

Suppose we have an Employees table:

employee_idemployee_namedepartment
101RahulIT
102PriyaHR
103AmitSales
104NehaFinance
105RaviIT
106SnehaMarketing

Suppose you want to retrieve employees who work in the IT, HR, or Sales departments. Run the following SQL query for this:

SELECT employee_id, employee_name, department
FROM Employees
WHERE department IN ('IT', 'HR', 'Sales');

Expected Output:

employee_idemployee_namedepartment
101RahulIT
102PriyaHR
103AmitSales
105RaviIT

In this example:

  • The statement SELECT employee_id, employee_name, department tells SQL to return the employee ID, employee name, and department.
  • The statement FROM Employees retrieves data from the Employees table.
  • WHERE department IN ('IT', 'HR', 'Sales'); apply the filtering condition.
  • The WHERE clause filters the rows.
  • The IN operator checks whether the value in the department column matches any of these three values:
    • IT
    • HR
    • Sales

For example:

  • Rahul → IT → Match ✓ → Returned
  • Priya → HR → Match ✓ → Returned
  • Amit → Sales → Match ✓ → Returned
  • Neha → Finance → No match → Excluded
  • Ravi → IT → Match ✓ → Returned
  • Sneha → Marketing→ No match → Excluded

SQL IN Operator with Numeric Values

You can use the SQL IN operator with numeric values to filter rows based on a list of numbers. This is useful when you want to retrieve records whose numeric column contains one of several specific values, such as employee IDs, product IDs, order IDs, or department IDs

Example: Filtering Employee IDs

Suppose you want to retrieve the employees whose IDs are 101, 103, or 105. Run the following SQL queries.

SELECT employee_id, employee_name, department
FROM Employees
WHERE employee_id IN (101, 103, 105);

Expected Output:

employee_idemployee_namedepartment
101RahulIT
103AmitSales
105RaviIT

In this example:

  • The SQL query SELECT employee_id, employee_name, department specifies the columns that SQL should return.
  • The SQL query FROM Employees retrieves data from the Employees table.
  • WHERE employee_id IN (101, 103, 105); applies the numeric filter.
  • The IN operator checks whether each employee_id matches one of these numeric values:
    • 101
    • 103
    • 105

The comparison can be understood as:

  • 101 → Match ✓ → Return row
  • 102 → No match → Exclude row
  • 103 → Match ✓ → Return row
  • 104 → No match → Exclude row
  • 105 → Match ✓ → Return row
  • 106 → No match → Exclude row

SQL IN Operator with Date Values

You can also use the SQL IN operator to filter records based on a specific set of dates. This can be useful when you need to retrieve orders, transactions, attendance records, appointments, or other records that occurred on one of several specific dates.

The basic idea is the same as with numeric and string values:

WHERE date_column IN (date1, date2, date3);

However, date and time handling requires additional care because date and timestamp data types differ between database systems.

Example: Filtering Orders by Specific Dates

Suppose we have an Orders table:

order_idcustomer_nameorder_dateamount
1001Rahul2026-10-012500
1002Priya2026-10-021800
1003Amit2026-10-053200
1004Neha2026-10-071500
1005Ravi2026-10-104200

Suppose you want to retrieve orders placed on October 1, October 5, or October 10, 2026. Run the following SQL query for this:

SELECT order_id, customer_name, order_date, amount
FROM Orders
WHERE order_date IN ('2026-10-01', '2026-10-05', '2026-10-10');

Expected Output:

order_idcustomer_nameorder_dateamount
1001Rahul2026-10-012500
1003Amit2026-10-053200
1005Ravi2026-10-104200

Using IN with a Date and Timestamp Column

When working with dates, you should first understand whether your column stores:

  • A date only (e.g., DATE data type)
  • A date and time (e.g., DATETIME or TIMESTAMP data type)

If order_date stores only dates, using the IN operator works as expected. However, suppose the column stores full timestamps such as:

  • 2026-10-01 09:15:00
  • 2026-10-01 14:30:00
  • 2026-10-01 18:45:00

If you write a condition like this:

WHERE order_date IN ('2026-10-01')

It may not return all records occurring on October 1, because a timestamp includes a time component. This is an important distinction when using IN with date/time data.

SQL IN Operator with Multiple Columns (Tuple Comparison)

The SQL IN operator can also be used with multiple columns simultaneously using a row-value (or tuple) comparison. This allows you to check whether a specific combination of column values matches one of several specified pairs or sets.

Example Scenario:

Suppose we have an EmployeeAssignments table:

employee_iddepartmentlocation
101ITDelhi
102HRMumbai
103SalesDhanbad
104ITPatna
105HRDelhi

We want to find employees whose department and location combination matches either:

  • IT + Delhi
  • Sales + Dhanbad

In database management systems that support row-value comparisons (like PostgreSQL, MySQL, and Oracle), you can write:

SELECT employee_id, department, location
FROM EmployeeAssignments
WHERE (department, location) IN (
    ('IT', 'Delhi'),
    ('Sales', 'Dhanbad')
);

Expected Output:

employee_iddepartmentlocation
101ITDelhi
103SalesDhanbad

How It Works Logically

SQL checks each row’s combination against your list:

  • (101, IT, Delhi) → Matches ('IT', 'Delhi') → Included
  • (102, HR, Mumbai) → No match ('HR', 'Mumbai') → Excluded
  • (103, Sales, Dhanbad) → Matches → Included
  • (104, IT, Patna) → No match → Excluded
  • (105, HR, Delhi) → No match → Excluded

SQL IN Operator with a Subquery

The IN operator becomes especially useful when the values to compare are stored in another table. Instead of manually writing a list of values, you can use a subquery to retrieve those values dynamically.

Example Scenario

Suppose we have two tables: Employees and Departments.

Employees Table:

employee_idemployee_namedepartment_id
101Rahul1
102Priya2
103Amit3
104Neha4
105Ravi1

Departments Table:

department_iddepartment_namelocation
1ITDelhi
2HRMumbai
3SalesDelhi
4FinanceDhanbad

Suppose we want to find employees who work in departments located in Delhi. We can use an IN subquery:

SELECT employee_id, employee_name, department_id
FROM Employees
WHERE department_id IN (
    SELECT department_id
    FROM Departments
    WHERE location = 'Delhi'
);

How Does This Query Work?

1. The Inner Query (Subquery) Runs First:

The database executes the subquery to find all department IDs where the location is Delhi:

SELECT department_id FROM Departments WHERE location = 'Delhi';

This returns the dataset: 1 and 3.

2. The Outer Query Evaluates:

The database then uses those results in the IN clause:

WHERE department_id IN (1, 3);

Expected Output:

employee_idemployee_namedepartment_id
101Rahul1
103Amit3
105Ravi1

Why Use a Subquery Instead of a Hard-Coded List?

You could technically write a static query like this:

WHERE department_id IN (1, 3);

However, this creates a maintenance problem. If a department’s location changes or a new department opens in Delhi next week, your hard-coded query will become outdated and return incorrect data.

With a subquery:

WHERE department_id IN (
    SELECT department_id
    FROM Departments
    WHERE location = 'Delhi'
);

the database automatically fetches the current, up-to-date matching department IDs every single time the query executes.

SQL NOT IN Operator

The NOT IN operator is the exact opposite of the IN operator in SQL. It checks whether a value does not match any value in a specified list or the result set returned by a subquery.

In simple terms:

  • IN asks: “Is this value in the list?”
  • NOT IN asks: “Is this value not in the list?”

If the value is absent from the specified list, the condition evaluates to TRUE, and SQL includes that row in the query result.

Basic Syntax

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

Example: NOT IN Operator

Consider the above Employees table. Suppose we want to find employees who do not work in IT or HR. We can write:

SELECT employee_id, employee_name, department
FROM Employees
WHERE department NOT IN ('IT', 'HR');

Expected Output:

employee_idemployee_namedepartment
103AmitSales
104NehaFinance
106SnehaMarketing

The above SQL query excludes employees whose department is either IT or HR.

Example: NOT IN with a Subquery

NOT IN operator in SQL can also work with a subquery. Suppose we want to find employees who do not belong to departments located in Delhi. Run the following SQL query to achieve this:

SELECT employee_id, employee_name, department_id
FROM Employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM Departments
    WHERE location = 'Delhi'
);

If the subquery returns 1 and 3, the outer query effectively checks:

WHERE department_id NOT IN (1, 3);

Therefore, employees belonging to departments 1 and 3 are excluded. Thus, you can use NOT IN operator when you want to exclude rows whose value matches any value in a specified list or subquery result.

The SQL IN Operator with NULL

The IN operator behaves differently when the list or subquery contains a NULL value. Since NULL represents an unknown value rather than an ordinary piece of data, it changes how expressions evaluate. Consider this example:

SELECT *
FROM Employees
WHERE department IN ('IT', 'HR', NULL);

It might look as though the query asks for employees whose department is IT, HR, or NULL. However, the NULL item does not work as a normal equality comparison.

SQL does not evaluate department = NULL as TRUE even when the department column is actually NULL. This happens because SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

Any standard comparison involving NULL (such as column = NULL) does not produce TRUE or FALSE; it produces UNKNOWN.

Example: IN Operator with NULL

Suppose we have an Employees table with the following data:

employee_idemployee_namedepartment
101RahulIT
102PriyaHR
103AmitSales
104NehaNULL

Now run the following SQL query.

SELECT employee_id, employee_name, department
FROM Employees
WHERE department IN ('IT', 'HR', NULL);

Expected Result:

employee_idemployee_namedepartment
101RahulIT
102PriyaHR

Notice that Neha is not returned, even though NULL appears in the IN list.

Key Takeaway

NULL inside an IN list or subquery does not match rows where the compared column is NULL. Use IS NULL when you specifically need to find NULL values.

DEEPAK GUPTA

DEEPAK GUPTA

Deepak Gupta is the Founder of Scientech Easy, a Full Stack Developer, and a passionate coding educator with 8+ years of professional experience in Java, Python, web development, and core computer science subjects. With strong expertise in full-stack development, he provides hands-on training in programming languages and in-demand technologies at the Scientech Easy Institute, Dhanbad.

He regularly publishes in-depth tutorials, practical coding examples, and high-quality learning resources for both beginners and working professionals. Every article is carefully researched, technically reviewed, and regularly updated to ensure accuracy, clarity, and real-world relevance, helping learners build job-ready skills with confidence.

Related Posts