Ranges, lists and patterns

BETWEEN, IN and LIKE make WHERE clauses shorter and easier to read.

Lesson 2 used =, <>, > and <. Three more operators cover most of the rest.

BETWEEN: a range

SELECT opportunity_id, name, amount
FROM opportunities
WHERE amount BETWEEN 10000 AND 50000;   -- includes 10000 and 50000

BETWEEN includes both ends. It’s shorthand for amount >= 10000 AND amount <= 50000.

Common mistake: BETWEEN with dates and times. closed_at BETWEEN '2026-01-01' AND '2026-12-31' misses anything that closed during December 31, because '2026-12-31' means midnight at the start of that day. Use a half-open range instead:

WHERE closed_at >= '2026-01-01'
  AND closed_at <  '2027-01-01'   -- before the start of next year

IN: a list

SELECT opportunity_id, name, lead_source
FROM opportunities
WHERE lead_source IN ('Inbound', 'Partner', 'Referral');

IN replaces a chain of ORs. NOT IN returns rows that match none of the list.

LIKE: a pattern

SELECT name
FROM accounts
WHERE name LIKE 'North%';

'%dental%' finds the word anywhere in the name.

Case sensitivity

Whether 'acme' matches 'Acme' depends on the database:

LOWER(name) LIKE '%acme%' works everywhere.

Snowflake notes

LIKE is case-sensitive in Snowflake. Use ILIKE for a case-insensitive match. Snowflake also has LIKE ANY ('North%', 'Acme%'), which matches any pattern in the list, so it works like IN for patterns.

Try it

Find opportunities created in 2026 with an amount between $5,000 and $20,000, from either the Inbound or Webinar lead source. Return name, amount and lead_source.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson