Pathubs SQL Curriculum • Module 05

SQL IN, BETWEEN & LIKE

Master practical SQL filtering: match discrete list elements with IN, query inclusive continuous ranges with BETWEEN, and perform text wildcard pattern matching with LIKE.

⏱️ Estimated Time:45 Minutes

🎯 Level:Beginner

📊 Track:Data Analytics & SQL Mastery

✨ Mode:Interactive Range & Wildcard Lab

Introduction

As your SQL queries handle more complex business questions, standard comparison operators (=, >, <) can result in long, cluttered, and error-prone code.

SQL provides three dedicated filtering operators that make queries shorter, clearer, and more expressive:

🎯 IN

Match a value against a discrete list of items.

📏 BETWEEN

Match numbers or dates within an inclusive range.

🔍 LIKE

Match text strings using flexible wildcard patterns.

The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. It returns TRUE if the column value matches any element in the provided list.

SELECT * FROM customers
WHERE city IN ('Mumbai', 'Delhi', 'Pune');

IN vs Multiple OR Conditions

Without IN, you would have to write repetitive OR statements:

-- ❌ Long and repetitive:
WHERE city = 'Mumbai' OR city = 'Delhi' OR city = 'Pune'

-- ✅ Clean and concise:
WHERE city IN ('Mumbai', 'Delhi', 'Pune')

IN With Numbers & NOT IN

IN works seamlessly with numbers without quotes, and NOT IN excludes specified list items:

SELECT * FROM employees
WHERE department_id IN (1, 3, 5);

SELECT * FROM employees
WHERE city NOT IN ('Mumbai', 'Delhi');

Diagram 1: IN List Evaluation Flow

Candidate Row: city = 'Delhi'

➔

List: ('Mumbai', 'Delhi', 'Pune')
Matches element #2!

➔

Row KEPT (TRUE)

The BETWEEN Operator

The BETWEEN operator filters rows within a specified range of numbers, text, or dates.

💡

Crucial Rule — BETWEEN is INCLUSIVE: In SQL, BETWEEN low AND high includes both the lower boundary and the upper boundary. salary BETWEEN 40000 AND 70000 includes an employee earning exactly ₹40,000 and an employee earning exactly ₹70,000.

-- BETWEEN vs Comparison Operators
WHERE salary >= 40000 AND salary <= 70000
-- is 100% equivalent to:
WHERE salary BETWEEN 40000 AND 70000;

BETWEEN With Dates & Timestamp Caution

When filtering dates such as WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31', keep in mind that date-time columns with timestamps (e.g. 2026-01-31 15:30:00) may fall outside if the upper bound defaults to midnight 2026-01-31 00:00:00.

Diagram 2: Inclusive BETWEEN Range Boundaries

₹30,000
Excluded (< min)

₹40,000 [MIN]
✓ INCLUDED

₹55,000
✓ Inside Range

₹70,000 [MAX]
✓ INCLUDED

₹80,000
Excluded (> max)

The LIKE Operator & Wildcards

The LIKE operator searches for specified patterns in string columns using two fundamental wildcards:

% (Percent Sign)

Matches zero, one, or multiple characters.

'A%' ➔ 'A', 'Aman', 'Ankit', 'Apex'

_ (Underscore)

Matches exactly one single character.

'_man' ➔ 'Aman', 'Iman' (Exactly 4 letters)

Common LIKE Patterns Quick Reference

Pattern	Meaning	Matching Examples
`'A%'`	Starts with letter 'A'	'Aman', 'Ankit', 'A'
`'%a'`	Ends with letter 'a'	'Priya', 'Sneha', 'Kavita'
`'%an%'`	Contains "an" anywhere	'Aman', 'Ankit', 'Rohan'
`'_a%'`	Second character is 'a'	'Rahul', 'Kavita', 'Rohan'

Diagram 3: Wildcard Character Matching

'A%'
'A' + (0 or more any characters)

'_a%'
(1 char) + 'a' + (0 or more any characters)

IN vs BETWEEN vs LIKE Decision Matrix

Operator	Best Used For	Data Type	SQL Example
IN	Specific, discrete list of known values	Strings, Numbers, IDs	`WHERE city IN ('Mumbai', 'Delhi')`
BETWEEN	Continuous, inclusive ranges	Numbers, Decimals, Dates	`WHERE price BETWEEN 1000 AND 5000`
LIKE	Fuzzy text search and prefix/suffix matching	Text / VARCHAR	`WHERE name LIKE 'Pro%'`

Combining With WHERE Clauses

You can combine these operators using standard logical connectors (AND / OR):

-- IN combined with BETWEEN
SELECT * FROM products
WHERE city IN ('Mumbai', 'Pune')
AND price BETWEEN 4000 AND 15000;

-- LIKE combined with IN
SELECT * FROM products
WHERE name LIKE 'A%'
AND category IN ('Electronics', 'Furniture');

⚡ Live Interactive SQL Filter Lab

⚙️ Filter ParametersInteractive Control

Select Cities to Include in city IN (...):

📦 Candidate Row Evaluations4 / 8 Passed

Product	Category	City	Price	Evaluation Status
Aman Pro Keyboard	Electronics	Mumbai	₹4,000	City 'Mumbai' in [Mumbai, Delhi] ➔ ✓ MATCH (Keep)
Ankit Ergonomic Chair	Furniture	Delhi	₹12,500	City 'Delhi' in [Mumbai, Delhi] ➔ ✓ MATCH (Keep)
Precision Mouse Pad	Accessories	Pune	₹800	City 'Pune' in [Mumbai, Delhi] ➔ ✗ NO MATCH (Drop)
Rahul HD Webcam	Electronics	Mumbai	₹6,500	City 'Mumbai' in [Mumbai, Delhi] ➔ ✓ MATCH (Keep)
Priya Standing Desk	Furniture	Bengaluru	₹24,000	City 'Bengaluru' in [Mumbai, Delhi] ➔ ✗ NO MATCH (Drop)
Apex Ultra Monitor	Electronics	Delhi	₹32,000	City 'Delhi' in [Mumbai, Delhi] ➔ ✓ MATCH (Keep)
Rohan Noise Canceller	Accessories	Bengaluru	₹15,000	City 'Bengaluru' in [Mumbai, Delhi] ➔ ✗ NO MATCH (Drop)
Zenith Studio Lamp	Furniture	Pune	₹4,000	City 'Pune' in [Mumbai, Delhi] ➔ ✗ NO MATCH (Drop)

✍️ Generated SQL Query● Synchronized

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

📋 Output Result Set4 Rows Kept (4 Dropped)

name	category	city	price
Aman Pro Keyboard	Electronics	Mumbai	₹4,000
Ankit Ergonomic Chair	Furniture	Delhi	₹12,500
Rahul HD Webcam	Electronics	Mumbai	₹6,500
Apex Ultra Monitor	Electronics	Delhi	₹32,000

🔍 Interactive Wildcard Pattern Visualizer

Test how any SQL LIKE pattern evaluates against candidate text step-by-step:

Pattern:

Candidate Word:

Pattern 'A%' checks if the text starts with 'A' followed by zero or more characters. Word 'Aman' matches the pattern! (✓ TRUE)

Common Beginner Mistakes

1. Missing Commas in IN Lists

Writing WHERE city IN ('Mumbai' 'Delhi') causes a syntax error. Separate all list items with commas.

2. Assuming BETWEEN Excludes Endpoints

Remember that BETWEEN 10 AND 20 includes 10 and 20. If you need exclusive boundaries, use > 10 AND < 20.

3. Confusing % with _

'A_' matches exactly 2-letter words starting with A (e.g. 'An'). To match any length, use 'A%'.

4. Forgetting Quotes on Text LIKE Patterns

Writing WHERE name LIKE A% is invalid syntax. String patterns must be enclosed in single quotes ('A%').

🧠 Interactive Challenge: Predict The Result (1 of 3)

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

How many total products match this IN query?

Practical Exercises

Task Goal	Target Table	Required SQL Solution	Operator Used
1. Filter Metro Cities	`customers`	`SELECT * FROM customers WHERE city IN ('Mumbai', 'Delhi', 'Bengaluru');`	`IN`
2. Mid-Range Products	`products`	`SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;`	`BETWEEN`
3. Names Starting with 'A'	`employees`	`SELECT * FROM employees WHERE name LIKE 'A%';`	`LIKE`
4. Text Containing 'Desk'	`products`	`SELECT * FROM products WHERE name LIKE '%Desk%';`	`LIKE`
5. Combined IN + BETWEEN	`orders`	`SELECT * FROM orders WHERE status IN ('Shipped', 'Delivered') AND amount BETWEEN 500 AND 2000;`	`IN` + `BETWEEN`
6. Combined LIKE + IN	`employees`	`SELECT * FROM employees WHERE name LIKE 'R%' AND department IN ('Sales', 'IT');`	`LIKE` + `IN`

Filtering Best Practices

Use IN for Readability: Whenever checking 2+ discrete values on the same column, use IN rather than multiple OR statements.
Remember BETWEEN Boundaries: Keep in mind that both bounds are included.
Avoid Leading % When Possible on Large Tables: Patterns like '%text' force a full table scan because indexes cannot be used for leading wildcards. Prefix patterns like 'text%' can leverage indexes in many database engines.
Keep Number Filtering on Numeric Types: Do not use LIKE to filter numbers; use BETWEEN or standard comparison operators.

What You Should Know Now

✓IN: Tests membership in a list of discrete values
✓NOT IN: Excludes rows matching any list element
✓BETWEEN: Filters ranges and includes both boundaries
✓NOT BETWEEN: Filters values outside the inclusive range
✓LIKE '%': Wildcard matching zero or more characters
✓LIKE '_': Wildcard matching exactly one single character

🎯 Knowledge Check Quiz: SQL IN, BETWEEN & LIKE

Test your understanding of list matching, range boundaries, and wildcard pattern evaluation.

1. Which SQL operator is designed to test if a column value matches any item in a specified list?

2. If you write "WHERE salary BETWEEN 50000 AND 80000", what happens to an employee with a salary of exactly 50000?

3. What does the wildcard "%" represent in a SQL LIKE pattern?

4. What does the wildcard "_" represent in a SQL LIKE pattern?

5. Which query correctly finds all products whose name begins with "Smart"?

6. Why is "city IN ('Mumbai', 'Delhi', 'Pune')" preferred over writing three separate OR conditions?

7. What does "WHERE city NOT IN ('Delhi', 'Pune')" return?

8. Which operator should you use if you need to find all orders placed between 1000 and 5000 units of currency?