Part 5: Mastering the IN Command | SQL Challenges & Capstone
SQL Filtering & Search Commands Practice · Part 5 · Lecture Notes
Mastering the IN Command
How to filter records using a list of values — replacing multiple OR conditions with a single, clean IN clause for business category selection.
Part 5 of 683% Complete
IN
List-Based Filtering
Multiple Values
OR Replacement
8 Practice Questions
Learning Objectives
Use IN to filter records matching any value in a specified list
Replace multiple OR conditions on the same column with a single, cleaner IN clause
Apply IN to category, status, and classification columns for business segment selection
Understand that IN returns rows matching any one value in the list
Click any section header to open or close it
IN filters records using multiple values inside a list. Instead of writing a separate OR condition for every value you want to match, IN accepts all of them in one compact clause. The database returns any row where the column value matches at least one value in the list.
In industry, analysts use IN to filter business categories, retrieve records with multiple statuses, simplify complex filtering logic, replace chains of repeated OR conditions, and analyze grouped business records across specified segments.
The Basic Structure
IN goes inside a WHERE clause. After IN, provide a comma-separated list of values in parentheses. Each value must be properly quoted if it is text. The database returns rows where the column matches any one of the listed values.
Basic IN Syntax
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2);
The real value of IN becomes clear when you compare it to the alternative. Without IN, filtering three countries requires three separate OR conditions. With IN, the same result is one readable clause. Both approaches produce identical results — IN is simply cleaner, shorter, and easier to maintain.
IN vs OR — Same Result, Less Code
WHERE country = ‘Canada’ OR country = ‘United States’ OR country = ‘United Kingdom’ produces exactly the same result as WHERE country IN (‘Canada’, ‘United States’, ‘United Kingdom’). IN is preferred for two or more values on the same column.
Users in Canada or the United States
SELECT *
FROM users
WHERE country IN (‘Canada’, ‘United States’);
IN is especially powerful on category and status columns where you want to include a specific subset of values. Product categories, order statuses, subscription tiers, ticket statuses, and campaign channels are all common IN filter targets in business analytics.
No Limit on List Size
IN can hold as many values as needed. IN (‘Software’, ‘Education’, ‘Service’, ‘Report’) selects all four categories in one clause. Each value is evaluated independently — the row is returned if any one of them matches.
Products in Software and Education Categories
SELECT *
FROM products
WHERE category IN (‘Software’, ‘Education’);
Subscriptions with Active or Cancelled Status
SELECT *
FROM subscriptions
WHERE status IN (‘Active’, ‘Cancelled’);
IN helps analysts isolate exactly the business segments they need for a report or investigation. Whether selecting specific countries for a regional analysis, specific statuses for a fulfilment review, or specific channels for a campaign comparison, IN provides a clean and readable way to define the scope of a query.
Support Ticket Triage Pattern
WHERE ticket_status IN (‘Open’, ‘Pending’) is a common support analytics query — it surfaces all unresolved tickets regardless of their exact status, without needing to know which specific statuses exist first.
Support Tickets with Open or Pending Status
SELECT *
FROM support_tickets
WHERE ticket_status IN (‘Open’, ‘Pending’);
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve users located in Canada and the United Kingdom
AI Prompt
Write a MySQL query to show users located in Canada and the United Kingdom.
SQL Answer
SELECT *
FROM users
WHERE country IN (‘Canada’, ‘United Kingdom’);
Practice 2Retrieve transactions with transaction types Purchase and Refund
AI Prompt
Write a MySQL query to show transactions with transaction types Purchase and Refund.
SQL Answer
SELECT *
FROM transactions
WHERE transaction_type IN (‘Purchase’, ‘Refund’);
Practice 3Retrieve products belonging to Software and Service categories
AI Prompt
Write a MySQL query to show products belonging to Software and Service categories.
SQL Answer
SELECT *
FROM products
WHERE category IN (‘Software’, ‘Service’);
Practice 4Retrieve orders with Completed and Pending statuses
AI Prompt
Write a MySQL query to show orders with Completed and Pending statuses.
SQL Answer
SELECT *
FROM orders
WHERE order_status IN (‘Completed’, ‘Pending’);
Practice 5Retrieve support tickets with Open and Pending statuses
AI Prompt
Write a MySQL query to show support tickets with Open and Pending statuses.
SQL Answer
SELECT *
FROM support_tickets
WHERE ticket_status IN (‘Open’, ‘Pending’);
Practice 6Retrieve subscriptions with Active and Cancelled statuses
AI Prompt
Write a MySQL query to show subscriptions with Active and Cancelled statuses.
SQL Answer
SELECT *
FROM subscriptions
WHERE status IN (‘Active’, ‘Cancelled’);
Practice 7Retrieve campaigns using Email and Paid Search channels
AI Prompt
Write a MySQL query to show campaigns using Email and Paid Search channels.
SQL Answer
SELECT *
FROM campaigns
WHERE channel IN (‘Email’, ‘Paid Search’);
Practice 8Retrieve sessions using Desktop and Mobile devices
AI Prompt
Write a MySQL query to show sessions using Desktop and Mobile devices.
SQL Answer
SELECT *
FROM sessions
WHERE device_type IN (‘Desktop’, ‘Mobile’);
Knowledge Check
Five questions — select the best answer for each.
1. What does IN do in a WHERE clause?
AIt sorts rows by a list of values
BIt filters records that match any value in a specified list
CIt counts the number of matching rows
DIt removes duplicate values from a column
Correct. IN returns any row where the column value matches at least one value in the list. It doesn’t sort, count, or deduplicate — it filters.
Not quite. IN filters records — it returns rows where the column value matches any value in the provided list.
2. Which query is equivalent to WHERE country = ‘Canada’ OR country = ‘United States’?
AWHERE country BETWEEN ‘Canada’ AND ‘United States’
BWHERE country LIKE ‘Canada’, ‘United States’
CWHERE country IN (‘Canada’, ‘United States’)
DWHERE country AND (‘Canada’, ‘United States’)
Correct. IN is the shorthand for multiple OR conditions on the same column. Both produce identical results; IN is cleaner and more readable.
Not quite. WHERE country IN (‘Canada’, ‘United States’) is the IN equivalent of multiple OR conditions on the same column.
3. Which part of a SQL query does IN belong to?
ASELECT
BFROM
CWHERE
DORDER BY
Correct. IN is always used inside the WHERE clause to filter which rows are returned. It cannot appear in SELECT, FROM, or ORDER BY.
Not quite. IN belongs in the WHERE clause — it is a filtering condition, not a retrieval or sorting command.
4. How many values can be listed inside an IN clause?
AOnly two
BA maximum of five
CAs many as needed
DOnly one value per IN clause
Correct. IN has no practical limit on the number of values. You can list two, five, twenty, or more values — the database evaluates each independently.
Not quite. IN accepts as many values as needed — there is no practical limit on the list size.
5. An analyst writes WHERE ticket_status IN (‘Open’, ‘Pending’). What does this return?
AOnly tickets where status is both Open and Pending at the same time
BTickets where status is either Open or Pending
CTickets where status is neither Open nor Pending
DAll tickets, sorted by status
Correct. IN returns rows where the column matches any one of the listed values — Open OR Pending. A row only needs to match one value in the list.
Not quite. IN returns rows matching any value in the list — Open or Pending. A row only needs to match one.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
IN simplifies SQL filtering by allowing analysts to retrieve records using grouped business conditions instead of multiple OR statements. List values in parentheses, comma-separated. Text values must be quoted. A row is returned if it matches any one value in the list.
