MySQL IN

The IN operator checks whether a column value exists in a list of specified values.

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

This returns students whose course is either Python or Java.

The basic syntax is:

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

The values inside the parentheses form the list to be checked.

Text values are written inside quotes.

SELECT *
FROM students
WHERE course IN ('Python', 'Java', 'PHP');

This returns students from any of the three courses.

IN can also be used with numeric values.

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

This returns students whose age matches one of the listed values.

You can specify two possible values.

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

This is shorter than writing two OR conditions.

You can include many values in an IN list.

SELECT *
FROM students
WHERE course IN (
    'Python',
    'Java',
    'PHP',
    'JavaScript'
);

These two queries can express the same basic condition:

Using OR:

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java'
OR course = 'PHP';

Using IN:

SELECT *
FROM students
WHERE course IN ('Python', 'Java', 'PHP');

IN is often easier to read when checking many possible values.

IN is commonly used with WHERE to filter records.

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

Only students from the specified courses are returned.

NOT IN returns records whose value is not included in the specified list.

SELECT *
FROM students
WHERE course NOT IN ('Python', 'Java');

This excludes Python and Java students.

IN can be combined with AND.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
AND age > 20;

This returns Python or Java students older than 20.

IN can also be combined with OR.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
OR fee > 20000;

A record can match either condition.

NOT IN is the commonly used form for excluding a list.

SELECT *
FROM students
WHERE NOT course IN ('Python', 'Java');

This expresses the same basic exclusion as:

SELECT *
FROM students
WHERE course NOT IN ('Python', 'Java');

IN can be used to search for specific numeric or decimal values.

SELECT *
FROM students
WHERE fee IN (10000, 15000, 20000);

This returns records whose fee matches one of the listed values.

IN can be used to search for specific dates.

SELECT *
FROM students
WHERE admission_date IN (
    '2026-01-01',
    '2026-02-01',
    '2026-03-01'
);

This returns records matching any of the specified dates.

IN can be combined with ORDER BY.

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

The matching records are sorted by fee in descending order.

LIMIT can restrict the number of records returned.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
LIMIT 5;

This returns up to five matching records.

IN and LIKE can be used together for more specific filtering.

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

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

IN can be combined with BETWEEN.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
AND age BETWEEN 18 AND 25;

This filters by both course and age range.

IN is normally applied to a specific expression or column. For multiple columns, MySQL can compare row values.

SELECT *
FROM students
WHERE (course, age) IN (
    ('Python', 22),
    ('Java', 21)
);

This checks combinations of course and age.

IN can be used with UPDATE to modify selected groups of records.

UPDATE students
SET fee = fee + 1000
WHERE course IN ('Python', 'Java');

This updates students from Python or Java.

IN can also be used with DELETE.

DELETE FROM students
WHERE course IN ('PHP', 'JavaScript');

This deletes records belonging to the specified courses.

IN and NULL checks serve different purposes. To find NULL values, use IS NULL.

SELECT *
FROM students
WHERE course IN ('Python', 'Java')
AND mobile IS NULL;

This finds Python or Java students whose mobile value is NULL.

IN can be used with a table alias.

SELECT s.name, s.course
FROM students AS s
WHERE s.course IN ('Python', 'Java');

The alias s represents the students table.

IN can also compare a value with results returned by a subquery.

SELECT name
FROM students
WHERE course IN (
    SELECT course
    FROM courses
);

The subquery provides the list of values used by IN.

• Forgetting parentheses around the list
• Forgetting quotes around text values
• Using the wrong column
• Using IN when a range condition is more appropriate
• Not checking NULL behavior with NOT IN
• Using UPDATE or DELETE without checking the affected records

A simple IN workflow is:

1. Identify the column to search.
2. Prepare the list of allowed values.
3. Use the IN operator.
4. Test the query with SELECT.
5. Add AND, OR, ORDER BY, or LIMIT if required.

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

Without IN:

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java'
OR course = 'PHP';

With IN:

SELECT *
FROM students
WHERE course IN ('Python', 'Java', 'PHP');

The IN version is generally shorter and easier to maintain when checking one column against several values.

Suppose we want students from Python, Java, or PHP whose age is at least 18.

SELECT id, name, course, age, fee
FROM students
WHERE course IN ('Python', 'Java', 'PHP')
AND age >= 18
ORDER BY fee DESC;

This combines IN with a comparison condition and ORDER BY.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2),
    admission_date DATE
);

INSERT INTO students
(name, course, age, fee, admission_date)
VALUES
('Rahul', 'Python', 22, 15000.00, '2026-01-15'),
('Priya', 'Java', 21, 18000.00, '2026-02-10'),
('Amit', 'Python', 24, 12000.00, '2026-03-05'),
('Neha', 'PHP', 17, 10000.00, '2026-04-12'),
('Ravi', 'JavaScript', 26, 22000.00, '2026-05-20');

SELECT id, name, course, age, fee
FROM students
WHERE course IN ('Python', 'Java', 'PHP')
AND age >= 18
ORDER BY fee DESC;

This query returns students whose course is Python, Java, or PHP and whose age is at least 18.