Pattern Matching: LIKE, IN, BETWEEN

Beyond Exact Matches

So far, your WHERE clauses check for exact matches: department = 'HR', salary = 90000. But real questions are often fuzzier:

• Find products that start with 'Sh'
• Get orders from any of these three months
• Show salaries between 50,000 and 100,000

That's what LIKE, IN, and BETWEEN are for. They give you flexible ways to match patterns and ranges.

LIKE: Pattern Matching for Text

LIKE lets you search for patterns in text using two wildcard characters:

• % — matches any sequence of characters (including none)
• _ — matches exactly one character

Pattern Matching with LIKE

SELECT *
FROM orders
WHERE order_details LIKE 'Sh%';

The % after 'Sh' means "Sh followed by anything." So Shoes, Shirts, and Shorts would all match.

Ends With

SELECT
  first_name,
  last_name
FROM techcorp_workforce
WHERE last_name LIKE '%a';

SELECT
  first_name,
  last_name,
  department
FROM techcorp_workforce
WHERE first_name LIKE '%i%';

Single Character Matching with _

The underscore matches exactly one character. This is useful when you know the pattern length.

-- Find 3-letter product names
WHERE order_details LIKE '___'

-- Find names starting with any letter, then 'oe'
WHERE first_name LIKE '_oe'

Underscores are less common than %, but handy for fixed-length codes or IDs.

Practice: Underscore Wildcards

Case Sensitivity

In PostgreSQL, LIKE is case-sensitive. 'Sh%' won't match 'shoes' or 'SHIRTS'.

If you need case-insensitive matching, use ILIKE (PostgreSQL only) or LOWER():

-- PostgreSQL: ILIKE is case-insensitive
WHERE order_details ILIKE 'sh%'

-- Works in all dialects: convert to lowercase first
WHERE LOWER(order_details) LIKE 'sh%'

Practice: LIKE

IN: Matching a List of Values

Remember chaining ORs to check multiple values? IN does the same thing, but cleaner.

Instead of:

WHERE department = 'HR'
  OR department = 'Admin'
  OR department = 'Engineering'

Write:

WHERE department IN ('HR', 'Admin', 'Engineering')

Much better. Let's see it in action:

This returns all HR, Admin, and Account employees.

IN with Numbers

IN works with any data type, not just text:

SELECT
  id,
  cust_id,
  order_details
FROM orders
WHERE cust_id IN (3, 7, 15);

This gets all orders from customers with IDs 3, 7, or 15.

NOT IN: Excluding Values

Add NOT to exclude values from the list:

SELECT
  first_name,
  last_name,
  department
FROM techcorp_workforce
WHERE department NOT IN ('Admin');

This returns everyone except Admin employees.

Practice: IN

BETWEEN: Range Filtering

BETWEEN filters for values within a range, including both endpoints.

BETWEEN is Shorthand

BETWEEN is equivalent to using >= and <= with AND:

-- These are identical:
WHERE salary BETWEEN 70000 AND 100000

WHERE salary >= 70000
  AND salary <= 100000

BETWEEN is just cleaner to read.

BETWEEN with Dates

BETWEEN is especially useful for date ranges:

SELECT
  first_name,
  last_name,
  joining_date
FROM techcorp_workforce
WHERE joining_date BETWEEN '2022-01-01' AND '2022-12-31';

This gets all employees who joined in 2022. Now try it with orders — get all orders from March 2019.

Keep in mind that Oracle doesn't implicitly cast string literals to dates — use a DATE literal or TO_DATE(). The other three dialects accept the plain string format directly.

NOT BETWEEN

Exclude a range with NOT BETWEEN:

SELECT
  first_name,
  last_name,
  salary
FROM techcorp_workforce
WHERE salary NOT BETWEEN 70000 AND 100000;

This returns employees earning less than 70,000 or more than 100,000.

Combine BETWEEN with other conditions.

Combining LIKE, IN, and BETWEEN

These operators work with AND and OR just like any other condition:

SELECT
  first_name,
  last_name,
  department,
  salary
FROM techcorp_workforce
WHERE department IN ('HR', 'Engineering')
  AND salary BETWEEN 80000 AND 200000
  AND first_name LIKE 'V%';

This finds HR or Engineering employees, earning 80k to 200k, whose first name starts with V.

Try It

Key Takeaways

• LIKE uses % (any characters) and _ (single character) for pattern matching.
• IN checks if a value matches any item in a list.
• BETWEEN filters for values within a range (inclusive on both ends).
• All three can be negated with NOT.
• These operators combine with AND/OR like any other condition.

What's Next

There's one more filtering concept to cover: NULL values. They behave differently than you might expect, and handling them correctly is essential. That's the focus of the next lesson.