IN Operator in SQL

The IN operator allows you to specify multiple values inside a WHERE condition.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi', 'Mumbai');

This returns students whose city is Patna, Delhi, or Mumbai.

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

The values inside the parentheses are the values that SQL will compare with the selected column.

The IN operator can be used with text values such as city names, course names, departments, and statuses.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Gaya', 'Delhi');

This returns students from Patna, Gaya, or Delhi.

IN can also be used with numeric values.

SELECT *
FROM Students
WHERE age IN (18, 20, 22);

This returns students whose age is 18, 20, or 22.

IN is very useful when you want to search for students belonging to several courses.

SELECT name, course
FROM Students
WHERE course IN ('Python', 'Java', 'PHP');

This returns students enrolled in Python, Java, or PHP.

The following query uses multiple OR conditions:

SELECT *
FROM Students
WHERE city = 'Patna'
OR city = 'Delhi'
OR city = 'Mumbai';

The same condition can be written using IN:

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi', 'Mumbai');

IN provides a shorter and easier way to write this type of condition.

IN is commonly used with the WHERE clause.

SELECT name, course
FROM Students
WHERE course IN ('Python', 'Java');

Only students studying Python or Java are returned.

You can use IN with SELECT * to retrieve all columns from matching records.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi');

All columns of students from Patna or Delhi are returned.

You can select only the columns that you need.

SELECT name, mobile, city
FROM Students
WHERE city IN ('Patna', 'Gaya');

This displays the student's name, mobile number, and city.

IN can be used to search for multiple student IDs.

SELECT *
FROM Students
WHERE student_id IN (101, 105, 110, 115);

This returns students whose IDs are present in the specified list.

IN can be used with numeric columns such as fees.

SELECT *
FROM Students
WHERE fee IN (5000, 10000, 15000);

This returns records whose fee is exactly 5000, 10000, or 15000.

IN is useful when searching for employees from multiple departments.

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

This returns employees from the IT, HR, or Sales departments.

IN can be used in shopping or product management systems.

SELECT product_name, category
FROM Products
WHERE category IN ('Laptop', 'Mobile', 'Tablet');

This returns products belonging to the selected categories.

Use NOT IN when you want to exclude multiple specified values.

SELECT *
FROM Students
WHERE city NOT IN ('Patna', 'Delhi');

This returns students whose city is not Patna or Delhi.

NOT IN can be used to exclude particular courses.

SELECT *
FROM Students
WHERE course NOT IN ('Tally', 'PHP');

This returns students who are not enrolled in Tally or PHP.

IN can be combined with AND to apply additional conditions.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi')
AND age >= 18;

This returns students from Patna or Delhi who are at least 18 years old.

IN can also be combined with OR.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi')
OR course IN ('Python', 'Java');

This returns records that satisfy either condition.

IN can be combined with ORDER BY to sort the matching records.

SELECT name, city
FROM Students
WHERE city IN ('Patna', 'Delhi', 'Mumbai')
ORDER BY name ASC;

The matching students are sorted alphabetically by name.

SELECT name, fee
FROM Students
WHERE course IN ('Python', 'Java')
ORDER BY fee DESC;

This returns Python and Java students with the highest fee first.

In MySQL, LIMIT can be used to restrict the number of records returned.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi')
LIMIT 5;

This returns up to five matching records.

DISTINCT can be used with IN to display unique matching values.

SELECT DISTINCT city
FROM Students
WHERE city IN ('Patna', 'Delhi', 'Gaya');

This displays each matching city only once.

IN can be used with aggregate functions such as COUNT().

SELECT COUNT(*)
FROM Students
WHERE course IN ('Python', 'Java');

This counts students who are enrolled in Python or Java.

IN can be used when you want to match specific dates.

SELECT *
FROM Students
WHERE admission_date IN
('2026-01-10', '2026-02-15', '2026-03-20');

This returns records having one of the specified admission dates.

IN can also be used with a subquery. A subquery is a query written inside another SQL query.

SELECT *
FROM Students
WHERE course_id IN
(
    SELECT id
    FROM Courses
    WHERE fee > 10000
);

The inner query finds courses with fees greater than 10000, and the outer query finds students belonging to those courses.

You can use more than one IN condition in the same query.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi')
AND course IN ('Python', 'Java');

This finds students from the selected cities who are studying the selected courses.

IN and LIKE can be used together when you need different types of conditions.

SELECT *
FROM Students
WHERE course IN ('Python', 'Java')
AND name LIKE 'A%';

This finds Python or Java students whose names start with A.

A common mistake is forgetting quotation marks around text values.

Incorrect:

SELECT *
FROM Students
WHERE city IN (Patna, Delhi);

Correct:

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi');

Text values should normally be written inside quotes.

NULL represents a missing or unknown value. IN should not be used as a replacement for checking NULL values.

SELECT *
FROM Students
WHERE city IN ('Patna', 'Delhi');

Rows where city is NULL do not match this IN condition.

To check for NULL values, use IS NULL instead.

SELECT *
FROM Students
WHERE city IS NULL;

Suppose an institute wants to find students from Patna or Delhi who are studying Python or Java.

SELECT name, city, course
FROM Students
WHERE city IN ('Patna', 'Delhi')
AND course IN ('Python', 'Java');

This query uses two IN conditions to filter the students.

SELECT name, age, course, city, fee
FROM Students
WHERE city IN ('Patna', 'Delhi', 'Gaya')
AND course IN ('Python', 'Java')
AND age BETWEEN 18 AND 30
ORDER BY fee DESC;

This query:

• Finds students from Patna, Delhi, or Gaya.
• Allows Python or Java courses.
• Checks students between 18 and 30 years old.
• Sorts the results by fee from highest to lowest.