SQL BETWEEN, IN, and LIKE: Pattern Matching Guide

Every SQL query starts with selecting rows, but the real power comes when you filter precisely. Need invoices from Q1? Use BETWEEN. Want customers in three specific countries? Use IN. Searching for tracks with "love" in the title? Use LIKE. These three operators — BETWEEN, IN, and LIKE — are the workhorses of the WHERE clause and show up in virtually every real-world query you will write.

In this guide you'll learn how each operator works, see real examples on the Chinook music-store database, and practice with hands-on exercises on SQiLs.

Table of Contents

• BETWEEN — Filter by Range
• IN — Match a List of Values
• LIKE — Wildcard Pattern Matching
• Combining Operators
• Cheat Sheet
• Common Mistakes to Avoid
• Practice Makes Perfect
• Summary
• Frequently Asked Questions

BETWEEN — Filter by Range

BETWEEN selects rows where a value falls within an inclusive range. It works with numbers, dates, and text. Think of it as a shorthand for >= AND <=.

Syntax

SELECT columns
FROM table
WHERE column BETWEEN low_value AND high_value;

This is equivalent to:

WHERE column >= low_value AND column <= high_value

Numeric range — find mid-priced tracks

SELECT
  Name,
  Composer,
  UnitPrice
FROM Track
WHERE UnitPrice BETWEEN 0.99 AND 1.49
ORDER BY UnitPrice DESC
LIMIT 5;

All 3503 tracks in the Chinook database are priced at either $0.99 or $1.99, so this range catches the $0.99 tier.

Date range — invoices from early 2021

SELECT
  InvoiceId,
  InvoiceDate,
  Total
FROM Invoice
WHERE InvoiceDate BETWEEN '2021-01-01' AND '2021-03-31'
ORDER BY InvoiceDate
LIMIT 5;

BETWEEN is inclusive on both ends, so invoices on January 1 and March 31 are both included. This makes it perfect for quarterly or monthly reporting.

Try it yourself — Filter invoices from early 2021 →

NOT BETWEEN — exclude a range

Add NOT to reverse the filter:

SELECT InvoiceId, Total
FROM Invoice
WHERE Total NOT BETWEEN 1.00 AND 5.00
ORDER BY Total DESC
LIMIT 5;

IN — Match a List of Values

IN checks whether a value matches any item in a list. It replaces multiple OR conditions and keeps your queries clean and readable.

Syntax

SELECT columns
FROM table
WHERE column IN (value1, value2, value3);

This is equivalent to:

WHERE column = value1 OR column = value2 OR column = value3

Filter customers by country

SELECT
  FirstName,
  LastName,
  Country,
  City
FROM Customer
WHERE Country IN ('USA', 'Canada', 'Brazil')
ORDER BY Country, LastName
LIMIT 5;

Instead of writing three separate OR conditions, IN lets you list all target values in one clean expression. This becomes especially valuable when the list grows to 5, 10, or more values.

Try it yourself — Find customers in USA or Canada →

Filter by numeric IDs

SELECT
  GenreId,
  Name
FROM Genre
WHERE GenreId IN (1, 2, 3, 4, 6);

IN works equally well with numbers, strings, and even subqueries. When paired with a subquery, it becomes a powerful filtering tool — but that's a topic for the subqueries guide.

Try it yourself — Filter for specific genre IDs →

NOT IN — exclude specific values

SELECT
  FirstName,
  LastName,
  Country
FROM Customer
WHERE Country NOT IN ('USA', 'Canada', 'Brazil')
ORDER BY Country
LIMIT 5;

LIKE — Wildcard Pattern Matching

LIKE searches for patterns inside text columns. It uses two wildcard characters:

Syntax

SELECT columns
FROM table
WHERE column LIKE pattern;

Find tracks starting with "The"

SELECT
  Name,
  Composer
FROM Track
WHERE Name LIKE 'The %'
ORDER BY Name
LIMIT 5;

Using 'The %' (with a space after "The") ensures we match tracks starting with the word "The" as a separate word, not just any word beginning with "The" like "Theme" or "Therapy".

Try it yourself — Find tracks starting with The →

Search for a keyword anywhere in a string

SELECT
  Name
FROM Track
WHERE Name LIKE '%life%'
ORDER BY Name
LIMIT 5;

The % on both sides matches "life" anywhere in the track name. Note that SQLite's LIKE is case-insensitive by default, so this also matches "Life" and "LIFE". In PostgreSQL and other databases, use ILIKE for case-insensitive matching.

Try it yourself — Find songs ending in Life →

Underscore wildcard — single character

SELECT Name
FROM Track
WHERE Name LIKE 'B_d%'
ORDER BY Name
LIMIT 5;

The _ matches exactly one character, so 'B_d%' finds "Bad", "Bed", "Bid", etc. followed by anything.

NOT LIKE — exclude a pattern

SELECT FirstName, LastName, Email
FROM Customer
WHERE Email NOT LIKE '%gmail%'
ORDER BY LastName
LIMIT 5;

Combining Operators

The real power shows up when you combine BETWEEN, IN, and LIKE with AND/OR in your WHERE clause.

Find rock or jazz tracks with "love" in the title

SELECT
  t.Name AS Track,
  g.Name AS Genre
FROM Track t
JOIN Genre g ON g.GenreId = t.GenreId
WHERE g.Name IN ('Rock', 'Jazz')
  AND t.Name LIKE '%Love%'
ORDER BY t.Name
LIMIT 5;

High-value invoices from specific countries in 2022

SELECT
  InvoiceId,
  BillingCountry,
  InvoiceDate,
  Total
FROM Invoice
WHERE BillingCountry IN ('USA', 'Canada')
  AND InvoiceDate BETWEEN '2022-01-01' AND '2022-12-31'
  AND Total > 5.00
ORDER BY Total DESC
LIMIT 5;

Each operator handles one aspect of filtering: IN narrows to specific countries, BETWEEN defines the date window, and a simple comparison sets the minimum total. Together they pinpoint exactly the rows you need.

Try it yourself — Find European city customers →

Cheat Sheet

Common Mistakes to Avoid

1. Forgetting that BETWEEN is inclusive

BETWEEN includes both boundary values. If you want invoices strictly within a date range (excluding the endpoints), use > and < instead.

-- BETWEEN: includes Jan 1 AND Dec 31
WHERE InvoiceDate BETWEEN '2022-01-01' AND '2022-12-31'

-- Exclusive: does NOT include the boundary dates
WHERE InvoiceDate > '2022-01-01' AND InvoiceDate < '2022-12-31'

2. Using NULL with IN or NOT IN

NULL does not equal anything — not even itself. If your column contains NULLs, NOT IN can return unexpected results.

-- This silently excludes rows where Composer IS NULL
SELECT Name FROM Track
WHERE Composer NOT IN ('U2', 'Metallica');

-- Safer: handle NULLs explicitly
SELECT Name FROM Track
WHERE Composer NOT IN ('U2', 'Metallica')
   OR Composer IS NULL;

Try it yourself — Find tracks with missing composers →

3. Mixing up % and _ wildcards

% matches zero or more characters. _ matches exactly one. Using _ when you mean % returns far fewer results than expected.

-- Wrong: matches only 4-character names starting with "The"
WHERE Name LIKE 'The_'

-- Right: matches anything starting with "The"
WHERE Name LIKE 'The%'

4. Assuming LIKE is always case-insensitive

SQLite's LIKE is case-insensitive for ASCII by default, but PostgreSQL's LIKE is case-sensitive. Use ILIKE in PostgreSQL or LOWER() for portable case-insensitive matching.

-- SQLite: works case-insensitively
WHERE Name LIKE '%love%'

-- PostgreSQL: use ILIKE for case-insensitive
WHERE Name ILIKE '%love%'

-- Portable: wrap both sides in LOWER()
WHERE LOWER(Name) LIKE '%love%'

5. Putting BETWEEN values in the wrong order

The low value must come first. BETWEEN 100 AND 1 returns zero rows — it does not auto-reverse.

-- Wrong: returns nothing
WHERE Total BETWEEN 100 AND 1

-- Right: low value first
WHERE Total BETWEEN 1 AND 100

Practice Makes Perfect

The best way to master BETWEEN, IN, and LIKE is to write real queries. Here are exercises that target exactly these operators:

Join 500+ SQL learners already practicing on SQiLs — Browse all exercises →

Summary

• BETWEEN filters values within an inclusive range — perfect for dates, prices, and numeric thresholds.
• IN matches a value against a list — cleaner and faster than chaining multiple OR conditions.
• LIKE searches for text patterns using % (any characters) and _ (one character) wildcards.
• All three operators work inside the WHERE clause and can be combined with AND/OR for precise filtering.
• Add NOT before any operator to reverse the condition: NOT BETWEEN, NOT IN, NOT LIKE.
• Watch out for NULL behavior with NOT IN — always handle NULLs explicitly.
• Once you're comfortable filtering rows, level up to GROUP BY and HAVING to start aggregating your filtered data, or explore JOINs to combine data from multiple tables.

Ready to put this into practice? Start solving exercises on SQiLs →

Frequently Asked Questions

What is the difference between LIKE and = in SQL?

The = operator matches an exact value, while LIKE supports wildcards (% and _) for pattern matching. Use = when you know the exact value (e.g., Country = 'USA') and LIKE when you need partial matches (e.g., Name LIKE '%love%'). = is faster because it can always use an index, while LIKE with a leading % requires a full table scan.

Can BETWEEN work with dates in SQL?

Yes, BETWEEN works well with dates. Write WHERE InvoiceDate BETWEEN '2022-01-01' AND '2022-12-31' to find all invoices in 2022. Remember that BETWEEN is inclusive — both boundary dates are included in the results. For datetime columns, be careful with the end date: '2022-12-31' might miss records timestamped later in that day.

Is SQL LIKE case-sensitive?

It depends on your database. In SQLite, LIKE is case-insensitive for ASCII characters by default. In PostgreSQL, LIKE is case-sensitive — use ILIKE instead. In MySQL, it depends on the column's collation. For portable code, wrap both sides in LOWER(): WHERE LOWER(Name) LIKE '%love%'.

When should I use IN vs multiple OR conditions?

Always prefer IN over chaining OR conditions. IN ('USA', 'Canada', 'Brazil') is more readable, easier to maintain, and performs identically to three OR conditions. The database optimizer treats them the same way. IN also works with subqueries — WHERE CustomerId IN (SELECT CustomerId FROM Invoice) — which OR cannot replicate cleanly.