---
title: "Filter rows with WHERE and how nulls behave | Oplema"
description: "Learn to filter rows with WHERE in SQL and FILTER in Excel, why nulls fail every comparison and how to reconcile the rows you keep with those you reject."
url: "https://oplema.com/learn/data-preparation/filter-rows"
updated: "2026-11-04"
---

Lesson 9 of 15 · Lab

# Filtering rows: WHERE, conditions and nulls

A filter keeps the rows that meet a condition and drops the rest. In SQL, that's a WHERE clause. A null is neither true nor false, so a row with an unknown value fails every ordinary comparison, including "not equal".

Take three rows with statuses `booked`, `pending` and null. In SQL, `status <> 'pending'` returns one row, not two.

You'll write the rule for [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep), whose 16 invoices now have one row each. It wants booked invoices dated in Q3.

This rule starts its date range on June 1. INV-1016 has no date at all. Which output should it land in?

False. An empty date can't be between two dates, so the row fails the condition. A date rule always carries this decision about unknowns.

## Fix one date in a filter rule

1. This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, opens with **Filter** selected and its rule in **Expression**. **OUTPUT TRUE** opens first.
2. Select **Summarize**, the last block. It adds up the kept rows: 14 rows, 13,110. Q3 can't include June, though.
3. Select **Filter**. In **Expression**, double-click `06` in `2026-06-01` and type `07`. Then select the **Filter** block.
4. Read **OUTPUT TRUE**. INV-1001 has gone.
5. Open **OUTPUT FALSE**. Every row here should have a reason you can name.
6. Select **Summarize** again. `rows` and `total` should both be lower.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the two outputs hold

True keeps 13 rows totaling €11,910. False holds 3: INV-1001 from June, INV-1009 canceled, INV-1016 pending with no date. 13 + 3 = 16.

## Rejected rows are your reconciliation

Most tools throw away the False output. Here it's your reconciliation, because kept plus rejected has to equal what came in. Every rejected row should also have a reason you can say aloud. A row you can't explain means your rule is wider than your question.

The expression compares true dates, which works because they were [parsed from text](https://oplema.com/learn/data-preparation/parse-text-and-dates) first. See the [Filter reference](https://oplema.com/docs/strata/prep/filter). A filter on a table changes what later steps receive, while [a dashboard filter](https://oplema.com/learn/business-intelligence/filters) changes what one reader sees.

The rule removes €1,300 of pending invoices and €1,200 from June, taking the sales report's list value from €14,410 to €11,910, on 13 rows. That's still €1,742 above the finance report, and [joining tables by key](https://oplema.com/learn/data-preparation/joins) shows why.

## Common filter mistakes

- **Forgetting nulls.** To keep them, say so with `OR status IS NULL`. `= NULL` never matches, so use `IS NULL`.
- **Mixing `AND` and `OR` without parentheses.** `AND` binds first, so `a OR b AND c` means `a OR (b AND c)`.
- **Using `BETWEEN` on timestamps.** An end date of 2026-09-30 means midnight, so it misses the rest of that day.
- **Matching text exactly on messy values.** [Clean them](https://oplema.com/learn/data-preparation/cleanse-and-nulls) first.

## WHERE and Excel's FILTER

```
SELECT  *  FROM  invoices
WHERE  status  =  'booked'
  AND  invoice_on  BETWEEN  DATE  '2026-07-01'  AND  DATE  '2026-09-30'
```

SQL Server has no `DATE '...'` literal, so write `'2026-07-01'` there.

In Excel, `=FILTER(A2:C100, B2:B100="booked")` returns the matching rows, and AutoFilter hides the others. Excel differs on nulls, because a blank cell counts as different from `"pending"`, so `B2<>"pending"` keeps blanks.
