• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL IN

← Previous Next →

In MySQL, the IN operator is used in a WHERE clause to check if a value matches any value in a list or subquery. It is a simpler and more readable alternative to using multiple OR conditions.

The IN operator is especially useful when you want to filter rows based on a set of known values. For example, you might want to find students living in a specific set of cities, or employees belonging to certain departments.

Basic Syntax

The syntax for using the IN operator is:

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

  • column_name is the column you want to check.

  • value1, value2, value3 is the list of values to match.

Example:

SELECT name, city FROM students
WHERE city IN ('Mumbai', 'Pune');

  • Returns all students living in Mumbai or Pune.

  • This is simpler than writing:

SELECT name, city FROM students
WHERE city = 'Mumbai' OR city = 'Pune';

Using IN with Numbers

The IN operator works with numeric values as well:

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

  • Returns all students whose age is 18, 20, or 22.

  • This is particularly useful for filtering ranges without using BETWEEN.

Using IN with Subqueries

The IN operator can also be combined with subqueries to select values dynamically:

SELECT name, city FROM students
WHERE city IN (
    SELECT city FROM students
    WHERE age > 20
);

  • Returns students whose city matches any city where students are older than 20.

  • This allows dynamic filtering based on other table conditions.

Using NOT IN

To exclude certain values, use NOT IN:

SELECT name, city FROM students
WHERE city NOT IN ('Mumbai', 'Pune');

  • Returns students not living in Mumbai or Pune.

  • Equivalent to multiple AND conditions:

WHERE city <> 'Mumbai' AND city <> 'Pune';

IN with Strings and Case Sensitivity

  • By default, MySQL string comparisons are case-insensitive.

  • Example:

SELECT name FROM students
WHERE city IN ('delhi', 'mumbai');

  • Will return students living in Delhi or Mumbai, regardless of capitalization.

  • To make it case-sensitive, use BINARY:

SELECT name FROM students
WHERE BINARY city IN ('delhi', 'mumbai');

IN vs OR

The IN operator is often more concise and readable than multiple OR statements:

-- Using OR
SELECT name FROM students
WHERE city = 'Mumbai' OR city = 'Pune' OR city = 'Delhi';

-- Using IN
SELECT name FROM students
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

  • Both return the same result, but IN is easier to maintain when the list grows.

IN with GROUP BY and ORDER BY

You can use IN with GROUP BY to aggregate filtered data:

SELECT city, COUNT(*) AS total_students
FROM students
WHERE city IN ('Mumbai', 'Pune')
GROUP BY city;

  • Returns the count of students in Mumbai and Pune.

Combine IN with ORDER BY to sort results:

SELECT name, city FROM students
WHERE city IN ('Mumbai', 'Pune')
ORDER BY name ASC;

  • Returns students in the selected cities sorted alphabetically by name.

Practical Scenarios

  1. Filtering students by a set of cities:

SELECT name FROM students
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

  • Quickly selects students living in the major cities.

  1. Filtering products by categories:

SELECT product_name FROM products
WHERE category_id IN (1, 3, 5);

  • Returns products in specific categories without multiple OR conditions.

  1. Filtering based on dynamic values using subqueries:

SELECT name FROM students
WHERE city IN (
    SELECT city FROM students
    WHERE age > 20
);

  • Selects students from cities where older students are located.

  1. Excluding specific values:

SELECT name FROM students
WHERE city NOT IN ('Pune', 'Delhi');

  • Returns students living in all other cities except Pune and Delhi.

Important Points

  1. IN is equivalent to multiple OR conditions but is cleaner and more readable.

  2. NOT IN excludes the specified values.

  3. Works with numbers, strings, and subqueries.

  4. Case sensitivity can be controlled using BINARY.

  5. Avoid NULL values in NOT IN, as it may return no results if the list contains NULL.

  6. Can be combined with GROUP BY and ORDER BY for reporting purposes.

Real-Life Example

Suppose the students table contains:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Pune
3 Pooja 18 Pune
4 Riya 21 Delhi
5 Ananya 22 Mumbai

Example 1 – Students in Mumbai or Pune:

SELECT name FROM students
WHERE city IN ('Mumbai', 'Pune');

  • Returns: Aditi, Ananya, Meera, Pooja

Example 2 – Students not in Mumbai or Pune:

SELECT name FROM students
WHERE city NOT IN ('Mumbai', 'Pune');

  • Returns: Riya

Example 3 – Students aged 18, 20, or 22:

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

  • Returns: Meera, Pooja, Ananya

Example 4 – Dynamic city selection using a subquery:

SELECT name FROM students
WHERE city IN (
    SELECT city FROM students
    WHERE age > 20
);

  • Returns students living in cities where at least one student is older than 20.

Summary of the Tutorial

  • The IN operator simplifies checking whether a column matches a list of values.

  • Use NOT IN to exclude specific values.

  • IN works with numbers, strings, and subqueries, making it versatile.

  • It improves query readability and can replace multiple OR statements.

  • Combining IN with GROUP BY and ORDER BY helps generate structured reports.

Mastering the IN operator allows you to filter data efficiently, handle dynamic lists of values, and write cleaner, more maintainable SQL queries.

Practice Questions

  1. Retrieve all students living in Mumbai, Pune, or Delhi.

  2. Retrieve all students whose age is 18, 20, or 22.

  3. Retrieve students whose city is not in Mumbai or Pune.

  4. Retrieve all products belonging to categories 1, 3, or 5.

  5. Retrieve all employees whose department ID is in 2, 4, 6.

  6. Retrieve students whose name is in ('Aditi', 'Meera', 'Riya').

  7. Retrieve students whose city is in the list of cities where age > 20.

  8. Retrieve students whose age is not in (19, 21).

  9. Retrieve all orders whose status is in ('Pending', 'Processing', 'Shipped').

  10. Count the number of students whose city is in ('Mumbai', 'Delhi').

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top