Lesson 3 of 20

In progress

Course progress

SQL

WHERE: filtering rows

Compare values, combine conditions, match text with LIKE, and handle NULL without falling into three-valued logic traps.

Filtering is where SQL starts answering questions instead of dumping tables. It is also where the most common beginner bug lives: a condition that silently matches nothing because of NULL.

What you will learn

  • Combine comparisons with AND, OR and NOT.
  • Use IN, BETWEEN and LIKE correctly.
  • Predict what NULL does in a condition.

Comparisons

SQL
Editable. Runs in your browser.





Press Run to see the output.

Comparisons use =, <> (or !=), <, >, <=, >=. They work on numbers, text and dates. Text comparison depends on collation, which determines whether case matters; the default in SQLite is case sensitive for =.

Filtering on a computed value repeats the expression rather than the alias, because WHERE is evaluated before the SELECT list is named:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Some databases allow the alias in WHERE; SQLite does not, and relying on it makes the query non-portable. Repeat the expression or use a subquery.

Combining conditions

SQL
Editable. Runs in your browser.





Press Run to see the output.

AND requires both sides, OR requires either, NOT inverts. Precedence puts AND before OR, so this:

WHERE status = 'paid' OR status = 'pending' AND amount_paise > 100000

means paid rows, plus pending rows above the amount. If you meant either status with the amount limit, use parentheses. Parentheses are free and remove the question.

Lists and ranges

SQL
Editable. Runs in your browser.





Press Run to see the output.

IN is shorter than a chain of ORs, and it also handles a list produced by a subquery. BETWEEN is inclusive on both ends, which surprises people who expect an exclusive upper bound. For dates, BETWEEN '2025-02-01' AND '2025-02-28' includes the 28th but not the time after it if the column has a time; use >= and < with the next day instead.

Matching text

SQL
Editable. Runs in your browser.





Press Run to see the output.

% matches any run of characters, _ matches exactly one. LIKE is case insensitive for ASCII in SQLite by default; in PostgreSQL it is case sensitive and ILIKE is the insensitive form.

A leading % prevents an index from being used, because the engine cannot know where the match starts. For a search box over large tables, that is the difference between milliseconds and seconds.

The NULL trap

SQL
Editable. Runs in your browser.





Press Run to see the output.

This returns no rows, ever. NULL means unknown, so city = NULL evaluates to unknown rather than true, and WHERE keeps only true.

SQL
Editable. Runs in your browser.





Press Run to see the output.

IS NULL and IS NOT NULL are the only correct tests. The same applies to <>: city <> 'Pune' excludes rows where city is null, because the comparison is unknown. If you want them, add OR city IS NULL.

NOT IN has a sharper version of this problem. x NOT IN (1, 2, NULL) is never true, because comparing with the NULL yields unknown. Avoid NULLs in NOT IN lists, or use NOT EXISTS.

Order of evaluation

WHERE is not necessarily evaluated left to right. The query planner may reorder conditions, so do not rely on short-circuit behaviour to guard against a division by zero or a bad cast. Use CASE when a guard is needed: a condition that is always evaluated, in a fixed position, is easier to reason about than an order you cannot see.

Using != or <> to find "everything except this value" and forgetting that NULL rows disappear. Add OR column IS NULL when those rows belong in the answer.

Find paid orders from Pune or Mumbai above 100000 paise, and separately find orders with no city recorded. Then explain why WHERE city <> 'Pune' misses one row.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

The second query returns Meera’s order. WHERE city <> 'Pune' returns Sameer, Ravi and Ana in Mumbai but not Meera, because NULL <> 'Pune' is unknown, not true. To include her, write WHERE city <> 'Pune' OR city IS NULL.

Recap

  • AND binds tighter than OR; add parentheses when the intent matters.
  • IN and BETWEEN shorten range tests, and BETWEEN includes both ends.
  • NULL is unknown: use IS NULL, and expect it to vanish from <> and NOT IN.

Next, you will sort and page through results with ORDER BY and LIMIT.

Is WHERE or HAVING faster?
They do different jobs. WHERE filters rows before grouping and is usually cheaper; HAVING filters groups and is the only option for aggregate conditions.
Can I filter on a CASE expression?
Yes. `WHERE CASE WHEN … END = ‘x’` works, though a plain boolean expression is usually clearer.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.