IN, BETWEEN, and LIKE Operators

Advanced filtering with IN, BETWEEN, and LIKE operators.

20 min read
Beginner

Introduction to Advanced Filtering

You've learned basic WHERE clauses with comparison operators (=, <, >, etc.). Now let's explore three powerful operators that make filtering more convenient and expressive:

  • IN - check if a value matches any in a list
  • BETWEEN - check if a value falls within a range
  • LIKE - check if text matches a pattern

These operators are shortcuts that make your SQL cleaner and more readable than using multiple OR conditions or complex comparisons.

Real-world scenarios:

  • "Find orders from these 5 specific customer IDs" → IN
  • "Show products priced between 50and50 and 100" → BETWEEN
  • "Find all customers whose name starts with 'A'" → LIKE
Operator Comparison
Operator
Purpose
Example
INMatch any value in a listWHERE city IN ('NYC', 'LA', 'Chicago')
BETWEENValue within a range (inclusive)WHERE price BETWEEN 10 AND 100
LIKEPattern matching with wildcardsWHERE name LIKE 'A%'

IN Operator - Matching Multiple Values

IN checks if a value matches any value in a list. It's a cleaner alternative to multiple OR conditions.

An operator that returns TRUE if a value matches any value in a specified list. Equivalent to multiple OR comparisons but more readable.

employees
id
name
department
salary
city
1AliceEngineering85000NYC
2BobMarketing65000LA
3CharlieSales70000Chicago
4DianaEngineering90000NYC
5EveSales75000Boston
6FrankMarketing68000LA

Find employees in specific departments:

sql
-- Using IN (clean and readable)
SELECT name, department
FROM employees
WHERE department IN ('Sales', 'Marketing');

Without IN (verbose and harder to read):

sql
-- Without IN: multiple OR conditions
SELECT name, department
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

Both queries return the same results, but IN is much cleaner, especially with many values!

IN with numbers:

sql
-- Find specific employees by ID
SELECT name, department
FROM employees
WHERE id IN (1, 3, 5);

Pro tip: IN can contain as many values as you need: 3, 10, 100, or more. Most databases handle large IN lists efficiently.

NOT IN - Excluding Values

NOT IN returns rows where the value does NOT match any value in the list.

sql
-- Find employees NOT in NYC or LA
SELECT name, city
FROM employees
WHERE city NOT IN ('NYC', 'LA');

Only Charlie (Chicago) and Eve (Boston) are returned. Everyone in NYC or LA is excluded.

NULL gotcha: If the IN list contains NULL, NOT IN can return unexpected results. NOT IN (1, 2, NULL) will never match any row because NULL comparisons are always unknown.

Safe approach: Filter out NULLs explicitly:

WHERE city NOT IN ('NYC', 'LA')
  AND city IS NOT NULL

IN with Subqueries

IN becomes even more powerful when combined with subqueries:

orders
order_id
customer_id
product_id
total
11011150.00
21022200.00
31013180.00
41031150.00
customers
customer_id
name
email
101Alicealice@example.com
102Bobbob@example.com
103Charliecharlie@example.com
104Dianadiana@example.com
sql
-- Find customers who have placed orders
SELECT name, email
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id FROM orders
);

The subquery returns customer IDs (101, 102, 103), then IN checks which customers match.

Find customers who have NOT placed orders:

sql
SELECT name, email
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

Diana (104) has no orders, so she's the only one returned.

BETWEEN Operator - Range Filtering

BETWEEN checks if a value falls within a range (inclusive of both endpoints).

An operator that checks if a value is within a specified range (inclusive). value BETWEEN low AND high is equivalent to value >= low AND value <= high.

products
product_id
product_name
price
stock
1Laptop999.9945
2Mouse29.99120
3Desk Chair199.9930
4Monitor349.9960
5Keyboard79.9995
6Desk Lamp39.9985

Find products in a price range:

sql
-- Products priced between $50 and $500 (inclusive)
SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 500;

Without BETWEEN (more verbose):

sql
-- Equivalent without BETWEEN
SELECT product_name, price
FROM products
WHERE price >= 50 AND price <= 500;

Same results, but BETWEEN is cleaner!

Important: BETWEEN is inclusive - it includes both 50 and 500. If you need exclusive ranges, use < and >:

sql
-- Exclusive range: price > 50 AND < 500
SELECT product_name, price
FROM products
WHERE price > 50 AND price < 500;

BETWEEN with Dates

BETWEEN works great with dates for time-based filtering:

orders_with_dates
order_id
customer_id
order_date
total
11012024-01-15150.00
21022024-01-20200.00
31012024-02-01180.00
41032024-02-10150.00
51022024-03-05220.00
sql
-- Orders placed in February 2024
SELECT order_id, order_date, total
FROM orders_with_dates
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

Find orders from the last 30 days:

sql
-- Last 30 days (assuming today is 2024-03-15)
SELECT order_id, order_date, total
FROM orders_with_dates
WHERE order_date BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

-- Or in PostgreSQL:
WHERE order_date BETWEEN NOW() - INTERVAL '30 days' AND NOW();

Date ranges caution: BETWEEN with dates includes the entire end date. BETWEEN '2024-02-01' AND '2024-02-29' includes all of February 29th (00:00:00 to 23:59:59).

NOT BETWEEN - Excluding Ranges

NOT BETWEEN returns values outside the specified range:

sql
-- Products NOT in the $50-$500 range (cheap or expensive)
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 50 AND 500;

Returns products priced under 50orover50 or over 500.

LIKE Operator - Pattern Matching

LIKE performs pattern matching on text using wildcards.

A pattern matching operator for text. Uses wildcards: % (matches any sequence of characters) and _ (matches exactly one character).

LIKE Wildcards
Wildcard
Meaning
Example
Matches
%Zero or more characters'A%'Alice, Apple, A, AB
_Exactly one character'A_'AB, A1, AX
customers_full
customer_id
name
email
phone
101Alice Smithalice@gmail.com555-1234
102Bob Johnsonbob@yahoo.com555-5678
103Charlie Browncharlie@gmail.com555-9012
104Diana Princediana@hotmail.com555-3456
105Alice Jonesajones@gmail.com555-7890

Find customers whose name starts with 'A':

sql
SELECT name, email
FROM customers_full
WHERE name LIKE 'A%';

'A%' matches any string starting with 'A' followed by anything (or nothing).

Find customers whose name ends with 'son':

sql
SELECT name, email
FROM customers_full
WHERE name LIKE '%son';

'%son' matches any string ending with 'son'.

Find customers with 'a' anywhere in their name:

sql
SELECT name, email
FROM customers_full
WHERE name LIKE '%a%';

'%a%' matches any string containing 'a' anywhere.

Case sensitivity: LIKE is case-insensitive in MySQL and SQL Server, but case-sensitive in PostgreSQL and SQLite. Use ILIKE in PostgreSQL for case-insensitive matching.

LIKE with Underscore Wildcard

The underscore (_) matches exactly one character:

sql
-- Find names with exactly 3 letters
SELECT name
FROM customers_full
WHERE name LIKE '___';

If Bob was the only 3-letter name, he'd be the only match. Three underscores = exactly 3 characters.

Phone numbers in specific format:

sql
-- Phone numbers starting with 555-12 (last two digits vary)
SELECT name, phone
FROM customers_full
WHERE phone LIKE '555-12__';

'555-12__' matches 555-12XX where XX can be any two digits.

Common LIKE Patterns

Pattern 1: Emails from a specific domain

sql
-- Gmail users
SELECT name, email
FROM customers_full
WHERE email LIKE '%@gmail.com';

Pattern 2: Names starting with specific letters

sql
-- Names starting with A, B, or C
SELECT name
FROM customers_full
WHERE name LIKE 'A%'
   OR name LIKE 'B%'
   OR name LIKE 'C%';

Pattern 3: Finding specific word patterns

sql
-- Products containing "Desk"
SELECT product_name
FROM products
WHERE product_name LIKE '%Desk%';

NOT LIKE - Excluding Patterns

NOT LIKE returns rows that do NOT match the pattern:

sql
-- Customers NOT using Gmail
SELECT name, email
FROM customers_full
WHERE email NOT LIKE '%@gmail.com';

Bob (Yahoo) and Diana (Hotmail) don't use Gmail.

Exclude names starting with 'A':

sql
SELECT name
FROM customers_full
WHERE name NOT LIKE 'A%';

Escaping Special Characters

What if you need to search for an actual % or _ character?

discounts
promo_code
description
SAVE1010% off
SAVE_2020% off
SPRING_SALE15% off
SUMMER2525% off
sql
-- Find promo codes with underscore in the name
SELECT promo_code, description
FROM discounts
WHERE promo_code LIKE '%\_%' ESCAPE '\';

ESCAPE '\' tells SQL that \ is the escape character. \_ means "literal underscore, not wildcard".

Find descriptions containing literal '%':

sql
-- Find text containing % character
SELECT promo_code, description
FROM discounts
WHERE description LIKE '%\%%' ESCAPE '\';

All descriptions contain '%', so all are returned.

Combining IN, BETWEEN, and LIKE

These operators work great together:

sql
-- Products priced $50-$500, in specific categories, with "e" in name
SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 500
  AND category IN ('Electronics', 'Furniture')
  AND product_name LIKE '%e%';

Complex customer search:

sql
-- High-value customers in specific cities, Gmail users
SELECT name, email, city
FROM customers_full
WHERE customer_id IN (
    SELECT customer_id FROM orders
    WHERE total BETWEEN 150 AND 250
  )
  AND city IN ('NYC', 'LA', 'Chicago')
  AND email LIKE '%@gmail.com';

Performance Considerations

IN operator:

  • Generally fast, especially with indexes
  • Large IN lists (1000+ values) can be slow
  • Consider temporary tables for very large lists

BETWEEN operator:

  • Very efficient with indexes on the column
  • Database can use index range scans
  • Much faster than separate >= and <= checks

LIKE operator:

  • Fast: LIKE 'ABC%' (starts with) - can use index
  • Slow: LIKE '%ABC' or LIKE '%ABC%' (contains) - full table scan
  • Leading wildcard (%) prevents index usage

Optimization tips:

  1. Use indexes on columns in WHERE with IN, BETWEEN, LIKE
  2. Avoid leading wildcards in LIKE when possible ('ABC%' is fast, '%ABC' is slow)
  3. Use full-text search for complex text searches instead of LIKE '%word%'
  4. Consider BETWEEN instead of >= AND <= for cleaner, potentially faster queries

Common Mistakes to Avoid

Don't make these mistakes:

  1. Confusing BETWEEN's inclusivity: ✅ Remember it's inclusive

    • BETWEEN 1 AND 10 includes both 1 and 10
    • For exclusive, use > 1 AND < 10

  2. NOT IN with NULL values: ❌ Dangerous!

    • WHERE col NOT IN (1, 2, NULL) may return no rows
    • Always filter NULLs: WHERE col NOT IN (...) OR col IS NULL

  3. LIKE without wildcards: ❌ Pointless

    • LIKE 'Alice' is the same as = 'Alice'
    • Use = for exact matches (it's faster)

  4. Wrong BETWEEN order: ❌

    • Wrong: BETWEEN 100 AND 10 (no results!)
    • Right: BETWEEN 10 AND 100 (low to high)

  5. Case sensitivity surprises: ⚠️

    • LIKE is case-sensitive in PostgreSQL, not in MySQL
    • Test your database's behavior or use UPPER/LOWER

  6. Overusing LIKE with leading %: ❌ Very slow

    • LIKE '%word%' scans entire table
    • Consider full-text search for better performance

  7. Forgetting to escape special characters: ❌

    • Searching for literal '_' or '%' requires escaping
    • Use ESCAPE clause: LIKE '%\_%' ESCAPE '\'

  8. Using IN with single value: Unnecessary

    • WHERE id IN (5) should be WHERE id = 5
    • IN is for multiple values

Practical Examples

Example 1: Product search with multiple filters

sql
-- Mid-priced electronics with specific keywords
SELECT product_name, price, category
FROM products
WHERE category IN ('Electronics', 'Computers')
  AND price BETWEEN 50 AND 300
  AND (product_name LIKE '%key%' OR product_name LIKE '%mouse%')
ORDER BY price;

Example 2: Customer segmentation

sql
-- VIP customers in major cities with Gmail
SELECT name, email, city
FROM customers_full
WHERE customer_id IN (
    SELECT customer_id FROM orders
    GROUP BY customer_id
    HAVING SUM(total) BETWEEN 500 AND 2000
  )
  AND city IN ('NYC', 'LA', 'Chicago', 'Boston')
  AND email LIKE '%@gmail.com'
ORDER BY name;

Example 3: Date-based order analysis

sql
-- Q1 2024 orders from specific customers
SELECT order_id, order_date, total
FROM orders_with_dates
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
  AND customer_id IN (101, 102, 105)
  AND total BETWEEN 100 AND 300
ORDER BY order_date DESC;

Key Takeaways

What you learned:

✅ IN checks if a value matches any in a list (cleaner than multiple ORs) ✅ NOT IN excludes values in the list (watch out for NULLs!) ✅ BETWEEN checks if value is in a range (inclusive of endpoints) ✅ NOT BETWEEN excludes values in the range ✅ LIKE performs pattern matching with wildcards (% and ) ✅ NOT LIKE excludes matching patterns ✅ % matches zero or more characters ✅ **** matches exactly one character ✅ IN works great with subqueries ✅ BETWEEN works with numbers, dates, and text ✅ Leading wildcards in LIKE (%) prevent index usage ✅ Use ESCAPE clause for literal % or _ characters

Performance tips:

  • IN and BETWEEN are index-friendly
  • LIKE with leading % requires full table scan
  • Avoid NOT IN with columns containing NULLs
  • Use = instead of LIKE when no wildcards needed

Coming up next: You'll explore window functions and PARTITION BY to perform calculations across groups of rows without collapsing them like GROUP BY does!

Practice Exercise: Try these queries:

  1. Find products with names starting with 'D' or 'K'
  2. List orders between January 1 and February 28, 2024
  3. Find customers in 'NYC', 'LA', or 'Boston' with Yahoo emails
  4. Show products priced 100−100-500 NOT in the Electronics category
  5. Find phone numbers starting with '555-1' (any last 3 digits)
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.