← Back to Chapters

MySQL IN Operator

?️ MySQL IN Operator

? Quick Overview

The IN operator in MySQL allows you to filter records by checking whether a column value matches any value in a given list or returned by a subquery. It is commonly used in PHP-MySQL applications to simplify database queries.

? Key Concepts

  • IN replaces multiple OR conditions
  • Works with fixed value lists or subqueries
  • NOT IN excludes matching values

? Syntax / Theory

? View Code Example 
// Basic syntax of MySQL IN operator
SELECT column_names FROM table_name
WHERE column_name IN (value1, value2, value3);

? Code Example 1: IN with Value List

This example fetches users who belong to selected cities.

? View Code Example 
// Select users from specific cities
SELECT * FROM users
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

? Code Example 2: IN with Subquery

The IN operator can also accept values returned by another query.

? View Code Example 
// Filter users based on cities from another table
SELECT * FROM users
WHERE city IN (SELECT city FROM offices WHERE department = 'HR');

? Code Example 3: NOT IN Operator

This query excludes users from certain cities.

? View Code Example 
// Exclude users from specific cities
SELECT * FROM users
WHERE city NOT IN ('New York', 'Los Angeles');

? Live Output / Explanation

The above queries return records depending on whether the city column matches (or does not match) values defined inside the IN list or subquery result.

? Interactive Understanding

Think of IN as a checklist. If the column value exists in the list, the row is selected — otherwise, it is ignored.

? Use Cases

  • Filtering users by multiple locations
  • Selecting products from specific categories
  • Matching records using results from another table

✅ Tips & Best Practices

  • Prefer IN over multiple OR conditions for cleaner queries
  • Use indexed columns for better performance
  • Ensure subqueries return a manageable number of rows

? Try It Yourself

  • Write a query to select products named Shirt or Pants
  • Find users not living in London, Paris, or Berlin
  • Use a subquery to filter orders by customer cities