# Types of Filters

Filter numbers and dates with comparison operators, text with LIKE patterns, lists with IN, ranges with BETWEEN, and missing values with IS NULL.

Apprentice · T-SQL · SQL · Updated November 3, 2026

## After this lesson you can

- Filter rows with comparisons, LIKE, IN, BETWEEN and IS NULL

## Key takeaways

- Compare numbers and dates with `=`, `<>`, `>`, `>=`, `<` and `<=`; write dates in single quotes, year first, as in `'2016-07-08'`.
- `LIKE` matches text against a pattern: `%` stands for any number of characters, even none, and `_` for exactly one.
- `IN` matches any value in a list, and `BETWEEN` matches a range, including both ends.
- `NULL` means unknown, so `= NULL` is never true: use `IS NULL` or `IS NOT NULL`.

Which filter you use depends on the kind of column, and the kind of question.

## Comparing numbers and dates

| Operator | Means |
|---|---|
| `=` | equal to |
| `<>` or `!=` | not equal to |
| `>` and `>=` | greater than, greater than or equal to |
| `<` and `<=` | less than, less than or equal to |

```sql
-- products that cost $50 or more
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice >= 50;
```

That returns 7 products, from Côte de Blaye at $263.50 down to Manjimup Dried Apples at $53.00.

Dates are written in single quotes, year first: `'2016-07-08'`. Then they compare like numbers, earlier being smaller:

```sql
-- orders placed on July 8, 2016
SELECT OrderId, CustomerId, OrderDate
FROM Orders
WHERE OrderDate = '2016-07-08';

-- orders placed in 2018 or later
SELECT OrderId, OrderDate
FROM Orders
WHERE OrderDate >= '2018-01-01';
```

The first returns two orders, 10250 and 10251. The second returns 270.

## Patterns in text: LIKE

`LIKE` matches text against a **pattern**, with two wildcards:

| Wildcard | Matches |
|---|---|
| `%` | any number of characters, even none |
| `_` | exactly one character |

```sql
-- employees whose first name starts with A
SELECT FirstName, LastName
FROM Employees
WHERE FirstName LIKE 'A%';
```

| FirstName | LastName |
|---|---|
| Andrew | Fuller |
| Anne | Dodsworth |

| Pattern | Finds |
|---|---|
| `'A%'` | starts with A |
| `'%s'` | ends with s |
| `'%Sauce%'` | contains Sauce anywhere |
| `'____'` | exactly four characters |
| `'The % of %'` | starts with "The ", and has " of " later, as in *The Lord of the Rings* |

```sql
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%Sauce%';
```

That finds Louisiana Fiery Hot Pepper Sauce and Northwoods Cranberry Sauce.

## A list of values: IN

`IN` matches any value in a list. It's shorter than writing `=` several times:

```sql
SELECT CompanyName, Country
FROM Customers
WHERE Country IN ('Spain', 'Portugal');
```

That returns 7 customers: 5 in Spain and 2 in Portugal.

## A range: BETWEEN

`BETWEEN` matches a range, **including** both ends:

```sql
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 10 AND 20;
```

That's the same as `UnitPrice >= 10 AND UnitPrice <= 20`, and it returns 29 products.

## Missing values: IS NULL

When a column has no value, it holds `NULL`: not zero, not an empty string, but *unknown*. Many Northwind customers have no `Region`. Because `NULL` is unknown, `= NULL` is never true. Use `IS NULL` and `IS NOT NULL` instead:

```sql
SELECT CompanyName, Country
FROM Customers
WHERE Region IS NULL;
```

That returns 60 customers. `WHERE Region = NULL` would return none.

## Check yourself

2 questions to try on the lesson page.

## So what

Comparisons, `LIKE`, `IN`, `BETWEEN` and `IS NULL` cover nearly every filter you'll write: pick the one that matches the kind of question you're asking.

## Key terms

- [LIKE Predicate](https://www.ask-codey.com/glossary/like-predicate)
- [Wildcard Character](https://www.ask-codey.com/glossary/wildcard-character)
- [IN Predicate](https://www.ask-codey.com/glossary/in-predicate)
- [BETWEEN Predicate](https://www.ask-codey.com/glossary/between-predicate)
- [NULL](https://www.ask-codey.com/glossary/null-database)
- [IS NULL Predicate](https://www.ask-codey.com/glossary/is-null-predicate)

## Cheat sheets

- [Types of Filters](https://www.ask-codey.com/cheat-sheets/where-in-t-sql-types-of-filters-cheat-sheet)

Source: https://www.ask-codey.com/lessons/where-in-t-sql-types-of-filters