WHERE and Comparison Operators

Filter early, filter cheap

The cheapest row is the one you never process. A staging model that only cares about paid orders should drop everything else at the WHERE step, before any join, sort, or aggregate runs. WHERE evaluates a condition once per row and keeps only the rows where that condition is TRUE.

In the warehouse this differs. Filtering early is the intuition behind predicate pushdown: the query planner tries to push your WHERE down to the source scan, so every downstream operator works on a smaller set. On a columnar warehouse a selective WHERE can also skip whole storage blocks it never has to read. WHERE is where that saving starts.

The comparison operators

= (equal), <> or != (not equal, where <> is the SQL-standard spelling), <, >, <=, >=. Text values go in single quotes (status = 'paid'); numbers are bare (total_cents >= 5000).

The boundary matters. >= 5000 keeps a row whose total is exactly 5000; > 0 excludes a row whose total is exactly 0. Choosing the wrong one is a classic off-by-one at the data layer. Combine conditions with AND (both must hold) or OR (either):

SELECT order_id, status, total_cents
FROM orders
WHERE status = 'paid'
  AND total_cents >= 5000;
-- order_id | status | total_cents
--    1     | paid   |    9900
--    4     | paid   |    5000   <- passes because >= is inclusive

Row 2 (paid, 1500) fails the amount test; rows 3 and 5 fail the status test.

Pitfall: single vs double quotes

Use single quotes for a string value. Double quotes mean identifier (a column or table name) in standard SQL. WHERE status = "paid" looks fine and even runs in SQLite, but only because there is no column named paid, so it quietly falls back to treating the text as a string. In Postgres the same query fails with column "paid" does not exist. Always write 'paid'.

When a filter drops rows you did not expect

Interview nuance: SQL uses three-valued logic. A predicate can be TRUE, FALSE, or UNKNOWN, and WHERE keeps a row only when the result is TRUE. Any comparison against NULL returns UNKNOWN, so if status is NULL for some rows, both status = 'paid' and status <> 'paid' drop them. That surprises people who assume a "not equal" filter returns everything that is not paid. To include or target those rows you need IS NULL / IS NOT NULL, which are separate operators. status = NULL never matches anything.

Recap

WHERE col op value keeps rows where the condition is TRUE. Quote text with single quotes, decide whether your boundary is inclusive (>=) or strict (>), chain checks with AND, and remember that NULL satisfies neither = nor <>.