WHERE & Operators
Overview
WHERE filters the row-set that FROM produced, keeping only rows where the condition evaluates to TRUE. Any row where the condition is FALSE or NULL is discarded. In the logical query flow: FROM builds the full row-set → WHERE removes rows that fail the test → SELECT picks the output columns. Comparison operators — =, <>, <, >, <=, >= — compare a column to a value: SELECT name, price FROM products WHERE price > 50 ORDER BY price; Returns 3 rows: Chair (89.99), Desk (149.99), Monitor (199.99). Logical operators — AND, OR, NOT — combine conditions: SELECT name, price FROM products WHERE category = 'Electronics' AND price < 50 ORDER BY name; Returns 2 rows: Keyboard (49.99), Mouse (19.99). BETWEEN tests an inclusive range: SELECT name, price FROM products WHERE price BETWEEN 20 AND 100 ORDER BY price; Returns 3 rows: Keyboard (49.99), Chair (89.99) — equivalent to price >= 20 AND price <= 100. Mouse (19.99) is excluded because 19.99 < 20. IN tests membership in a list: SELECT name, country FROM customers WHERE country IN ('US', 'UK') ORDER BY name; Returns 4 rows: Alice, Bob, Diana, Greg. LIKE matches text patterns (% = any characters, _ = one character): SELECT name FROM customers WHERE name LIKE 'A%' ORDER BY name; Returns 1 row: Alice. Key mental model: WHERE is a row-by-row TRUE/FALSE gate. It never changes values — it only decides which rows survive to the next stage.
Almost every real query filters data. Without WHERE, every query returns every row in the table — useless for any application that needs specific records. WHERE is also critical for UPDATE and DELETE safety: forgetting it updates or deletes every row.
Where used: API endpoints with search/filter parameters, dashboard queries with date ranges, UPDATE/DELETE safety guards
Why learn this
- Every filtered API list endpoint translates user parameters into WHERE conditions
- Understanding WHERE's TRUE/FALSE/NULL gate explains why NULL comparisons silently drop rows — a critical prerequisite for NULL handling
Query walkthrough
SELECT name, price
FROM products
WHERE category = 'Electronics' AND price < 50
ORDER BY name;Focus: Watch WHERE discard 4 of the 6 product rows — only rows where BOTH conditions are TRUE survive to SELECT.
Common mistakes
- Using = NULL instead of IS NULL: NULL is not a value — it means 'unknown'. The expression country = NULL evaluates to NULL (not TRUE), so the row is silently dropped. Use IS NULL or IS NOT NULL instead.
- Confusing AND/OR precedence: AND binds tighter than OR. The condition A OR B AND C is read as A OR (B AND C), not (A OR B) AND C. Use parentheses to make intent explicit.
- Forgetting BETWEEN is inclusive on both ends: BETWEEN 20 AND 100 includes both 20 and 100. If you want to exclude endpoints, use > and < instead.
Glossary
- discarded
- Thrown away or rejected; in SQL, rows that do not meet a certain condition and are removed from the results.
- inclusive
- Containing or including the endpoints or limits of a range, like counting from 1 to 10 and including both 1 and 10.
- membership
- The state of belonging to a specific group or list, like checking if a country is in a specific list of countries.
Recall questions
- At what stage in the logical query flow does WHERE run — before or after SELECT?
- What happens when a WHERE condition evaluates to NULL for a row?
- What do the % and _ wildcards mean in a LIKE pattern?
Understanding checks
Which customers does this return?
4 rows: Alice (US), Bob (US), Carlos (MX), Greg (US).
WHERE tests each row: country = 'US' is TRUE for Alice, Bob, Greg; country = 'MX' is TRUE for Carlos. OR keeps a row if either condition is TRUE. Emma's NULL country fails both tests (NULL = 'US' is NULL, not TRUE).
What rows does this return?
1 row: Monitor (199.99).
AND requires both conditions to be TRUE. Of the 3 Electronics products (Keyboard 49.99, Mouse 19.99, Monitor 199.99), only Monitor has price > 100.
Practice tasks
Widen the filter to include Furniture
This query returns only Electronics products under $50. Modify the WHERE clause so it also includes products in the 'Furniture' category (at any price). Keep the same columns and ordering.
Challenge
Orders in a date range
Write a query that returns the id and order_date of all orders placed between '2023-03-01' and '2023-06-30' (inclusive) with status 'paid'. Order by order_date.
Continue learning
Previous: SELECT, FROM & Columns
Next: ORDER BY, LIMIT & OFFSET
Next: DISTINCT
Next: Data Types & NULL Handling
Next: CASE Expressions