SQL 15 🛢️ Filtering Rows with WHERE
The SELECT statement retrieves rows. Without a WHERE clause, it retrieves every row in the table. With a WHERE clause, it retrieves only the rows that match a condition. The WHERE clause is the filter. It is the difference between “give me all the orders” and “give me the orders placed in the last thirty days by customers in New York.”
The previous chapter covered the SELECT and FROM clauses — the two required clauses of every query. This chapter covers the WHERE clause, which is the first optional clause and the most frequently used. The WHERE clause evaluates a boolean expression for each row. If the expression is TRUE, the row is included in the result. If the expression is FALSE or NULL, the row is excluded.
Key point: The WHERE clause is evaluated before the SELECT clause in the logical order of query processing. The database reads the rows from the tables in the FROM clause, filters them with the WHERE clause, then projects the columns specified in the SELECT clause. This order matters for understanding which columns can be referenced in the WHERE clause — only the columns from the FROM tables, not the aliases defined in the SELECT clause.
Why the WHERE clause matters
A table with a million rows is not useful if the application always retrieves all of them. The WHERE clause is how the application retrieves the rows it needs and only the rows it needs.
The relevance problem. A customer wants to see their own orders, not everyone’s orders. The WHERE clause filters the orders by customer_id. The result set contains only the customer’s orders.
The performance problem. Retrieving a million rows and filtering them in the application is slow. The database can filter the rows before sending them over the network. The WHERE clause reduces the amount of data transferred. With an index on the filtered column, the database can find the matching rows without scanning the entire table.
The correctness problem. A query that retrieves the wrong rows produces wrong results. The WHERE clause is the tool that ensures the query retrieves the correct rows.
The safety problem. An UPDATE or DELETE statement without a WHERE clause modifies or deletes every row in the table. The WHERE clause is what makes the operation safe. The same clause that filters a SELECT also filters an UPDATE or DELETE.
The trade-off. A WHERE clause with a complex condition is slower than one with a simple condition. The database must evaluate the expression for every row. An index on the filtered column makes the evaluation fast. Without an index, the database scans the entire table. The performance depends on the indexes and the selectivity of the condition.
a. The Basic Comparison Operators
The WHERE clause uses comparison operators to compare a column to a value. The standard operators are =, <> (or !=), >, >=, <, and <=.
SELECT * FROM customers WHERE city = 'New York';
SELECT * FROM orders WHERE total > 100;
SELECT * FROM products WHERE stock <= 10;
SELECT * FROM users WHERE is_active <> FALSE;The = operator tests for equality. The <> and != operators test for inequality. The >, >=, <, and <= operators test for ordering.
The comparison can be between a column and a literal, between two columns, or between a column and an expression.
SELECT * FROM orders WHERE total > 100;
SELECT * FROM products WHERE price > cost;
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);The first query compares a column to a literal. The second compares two columns. The third compares a column to the result of a subquery.
The WHERE clause can use string literals, numeric literals, date literals, and boolean literals. String literals are enclosed in single quotes. Date literals use the ISO format ('2026-01-01') or the database’s specific format.
SELECT * FROM orders WHERE order_date >= '2026-01-01';
SELECT * FROM users WHERE email = 'alice@example.com';b. The Logical Operators
The logical operators AND, OR, and NOT combine multiple conditions. The AND operator requires both conditions to be true. The OR operator requires at least one condition to be true. The NOT operator negates a condition.
-- AND: both conditions must be true
SELECT * FROM orders WHERE total > 100 AND order_date >= '2026-01-01';
-- OR: at least one condition must be true
SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago';
-- NOT: the condition must be false
SELECT * FROM users WHERE NOT is_active;The operators can be combined with parentheses to control the order of evaluation. The AND operator has higher precedence than OR. Without parentheses, the AND is evaluated first.
-- Without parentheses, the AND is evaluated first
SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago' AND is_active = TRUE;
-- With parentheses, the OR is evaluated first
SELECT * FROM customers WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE;The two queries produce different results. The first query selects customers in New York, plus customers in Chicago who are active. The second query selects customers in either city who are active.
The AND operator short-circuits. If the first condition is false, the second condition is not evaluated. The OR operator also short-circuits. If the first condition is true, the second condition is not evaluated. The short-circuit behavior matters when one of the conditions has a side effect or is expensive to evaluate.
c. The Range, Set, and Pattern Operators
The BETWEEN operator tests whether a value is in a range. The range is inclusive.
SELECT * FROM orders WHERE total BETWEEN 50 AND 200;The query is equivalent to total >= 50 AND total <= 200. The BETWEEN operator is cleaner and less error-prone.
The IN operator tests whether a value is in a set of values.
SELECT * FROM customers WHERE city IN ('New York', 'Chicago', 'Boston');The query is equivalent to city = 'New York' OR city = 'Chicago' OR city = 'Boston'. The IN operator is cleaner when the set is large.
The IN operator can also be used with a subquery.
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 200);The subquery returns a set of customer IDs. The outer query selects the customers with those IDs.
The LIKE operator tests whether a string matches a pattern. The % wildcard matches any sequence of characters. The _ wildcard matches any single character.
-- Names starting with 'A'
SELECT * FROM customers WHERE first_name LIKE 'A%';
-- Names ending with 'son'
SELECT * FROM customers WHERE last_name LIKE '%son';
-- Names containing 'li' anywhere
SELECT * FROM customers WHERE first_name LIKE '%li%';
-- Names with exactly three characters
SELECT * FROM customers WHERE first_name LIKE '___';The LIKE operator is case-sensitive in most databases. PostgreSQL has ILIKE for case-insensitive matching. MySQL’s LIKE is case-insensitive by default.
The IS NULL and IS NOT NULL operators test for null values. The = operator cannot be used with null because null is not equal to anything, including null.
SELECT * FROM customers WHERE city IS NULL;
SELECT * FROM customers WHERE city IS NOT NULL;The IS NULL operator is the only way to test for null. The = NULL comparison returns NULL, which is not TRUE, so the row is not included in the result.
Complete Example Session
This session demonstrates the WHERE clause on a small database.
-- ============================================
-- PART 1: THE TABLES
-- ============================================
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
city VARCHAR(50),
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO customers VALUES
(1, 'Alice', 'Johnson', 'alice@example.com', 'New York', TRUE),
(2, 'Bob', 'Smith', 'bob@example.com', 'Chicago', TRUE),
(3, 'Carol', 'Williams', 'carol@example.com', 'New York', FALSE),
(4, 'Dave', 'Brown', 'dave@example.com', NULL, TRUE),
(5, 'Eve', 'Davis', 'eve@example.com', 'Boston', TRUE);
INSERT INTO orders VALUES
(1001, 1, '2026-01-15', 99.99, 'shipped'),
(1002, 1, '2026-02-20', 149.99, 'pending'),
(1003, 2, '2026-01-10', 49.99, 'shipped'),
(1004, 3, '2026-03-05', 299.99, 'delivered'),
(1005, 4, '2026-03-10', 199.99, 'pending');
-- ============================================
-- PART 2: EQUALITY
-- ============================================
SELECT * FROM customers WHERE city = 'New York';
-- Output: Alice and Carol
-- ============================================
-- PART 3: COMPARISON
-- ============================================
SELECT * FROM orders WHERE total > 100;
-- Output: orders 1002, 1004, 1005
-- ============================================
-- PART 4: AND
-- ============================================
SELECT * FROM orders WHERE total > 100 AND status = 'pending';
-- Output: orders 1002, 1005
-- ============================================
-- PART 5: OR
-- ============================================
SELECT * FROM customers WHERE city = 'New York' OR city = 'Boston';
-- Output: Alice, Carol, Eve
-- ============================================
-- PART 6: NOT
-- ============================================
SELECT * FROM customers WHERE NOT is_active;
-- Output: Carol
-- ============================================
-- PART 7: BETWEEN
-- ============================================
SELECT * FROM orders WHERE total BETWEEN 50 AND 200;
-- Output: orders 1001, 1002, 1003, 1005
-- ============================================
-- PART 8: IN
-- ============================================
SELECT * FROM customers WHERE city IN ('New York', 'Boston');
-- Output: Alice, Carol, Eve
-- ============================================
-- PART 9: LIKE
-- ============================================
SELECT * FROM customers WHERE first_name LIKE 'A%';
-- Output: Alice
SELECT * FROM customers WHERE last_name LIKE '%son';
-- Output: Alice Johnson
-- ============================================
-- PART 10: IS NULL
-- ============================================
SELECT * FROM customers WHERE city IS NULL;
-- Output: Dave
SELECT * FROM customers WHERE city IS NOT NULL;
-- Output: Alice, Bob, Carol, EveThe ten parts cover the tables, equality, comparison, AND, OR, NOT, BETWEEN, IN, LIKE, and IS NULL.
Quick Reference
The Comparison Operators
| Operator | Purpose |
|---|---|
= | Equal to |
<> / != | Not equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
The Logical Operators
| Operator | Purpose |
|---|---|
AND | Both conditions true |
OR | At least one condition true |
NOT | Condition false |
The Range, Set, and Pattern Operators
| Operator | Purpose |
|---|---|
BETWEEN a AND b | Value in range |
IN (a, b, c) | Value in set |
LIKE 'pattern' | String matches pattern |
IS NULL | Value is null |
IS NOT NULL | Value is not null |
The LIKE Wildcards
| Wildcard | Matches |
|---|---|
% | Any sequence of characters |
_ | Any single character |
The Operator Precedence
| Precedence | Operators |
|---|---|
| 1 (highest) | =, <>, >, >=, <, <= |
| 2 | NOT |
| 3 | AND |
| 4 (lowest) | OR |
Best Practices
✅ Do This:
-- Use parentheses to clarify precedence
WHERE (city = 'New York' OR city = 'Chicago') AND is_active = TRUE -- ✅-- Use IS NULL for null checks
WHERE city IS NULL -- ✅-- Use IN for multiple values
WHERE city IN ('New York', 'Chicago', 'Boston') -- ✅-- Use BETWEEN for ranges
WHERE total BETWEEN 50 AND 200 -- ✅-- Use LIMIT with WHERE on large tables
WHERE total > 100 LIMIT 100 -- ✅❌ Don’t Do This:
-- Don't use = NULL
WHERE city = NULL -- never true -- ❌-- Don't forget parentheses with mixed AND/OR
WHERE city = 'NY' OR city = 'CHI' AND active = TRUE -- ⚠️-- Don't use functions on indexed columns in WHERE
WHERE UPPER(last_name) = 'SMITH' -- index not used -- ❌-- Don't use leading wildcards with LIKE
WHERE last_name LIKE '%son' -- index not used -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Null not matched | = NULL used | Use IS NULL |
| Wrong results | Missing parentheses | Add parentheses |
| Slow query | Function on indexed column | Rewrite to use the column directly |
| No index used | Leading wildcard | Avoid % at the start |
| Case mismatch | LIKE is case-sensitive | Use ILIKE or LOWER() |
Real-World Examples
1. Equality
SELECT * FROM customers WHERE city = 'New York';2. Not Equal
SELECT * FROM orders WHERE status <> 'cancelled';3. Greater Than
SELECT * FROM orders WHERE total > 100;4. AND
SELECT * FROM orders WHERE total > 100 AND status = 'pending';5. OR
SELECT * FROM customers WHERE city = 'New York' OR city = 'Chicago';6. BETWEEN
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';7. IN
SELECT * FROM customers WHERE city IN ('New York', 'Chicago', 'Boston');8. LIKE
SELECT * FROM customers WHERE email LIKE '%@example.com';9. IS NULL
SELECT * FROM customers WHERE city IS NULL;10. Subquery
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total > 200);Visual
The WHERE Clause
┌──────────────────────────────────────────────┐
│ SELECT * │
│ FROM customers │
│ WHERE city = 'New York' │
│ │
│ The WHERE clause filters the rows. │
│ Only the rows that match are returned. │
│ │
│ Rows: 5 │
│ Matching: 2 │
│ Result: 2 rows │
│ │
└──────────────────────────────────────────────┘The AND/OR Precedence
┌──────────────────────────────────────────────┐
│ WITHOUT PARENTHESES │
│ WHERE city = 'NY' OR city = 'CHI' │
│ AND active = TRUE │
│ │
│ Evaluated as: │
│ WHERE city = 'NY' OR (city = 'CHI' AND active = TRUE)│
│ │
│ WITH PARENTHESES │
│ WHERE (city = 'NY' OR city = 'CHI') │
│ AND active = TRUE │
│ │
│ Different results. │
│ │
└──────────────────────────────────────────────┘The Null Comparison
┌──────────────────────────────────────────────┐
│ NULL COMPARISON │
│ │
│ WHERE city = NULL │
│ └─ Returns NULL, not TRUE │
│ └─ No rows returned │
│ │
│ WHERE city IS NULL │
│ └─ Returns TRUE for null values │
│ └─ Rows returned │
│ │
│ Use IS NULL, not = NULL. │
│ │
└──────────────────────────────────────────────┘The LIKE Pattern
┌──────────────────────────────────────────────┐
│ LIKE PATTERNS │
│ │
│ 'A%' → starts with A │
│ '%son' → ends with son │
│ '%li%' → contains li │
│ '_a_' → three chars, middle is a │
│ 'A_%' → starts with A, at least 2 chars │
│ │
│ % matches any sequence │
│ _ matches any single character │
│ │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
WHERE | Filters rows by a condition |
= | Equal to |
<> / != | Not equal to |
AND | Both conditions true |
OR | At least one condition true |
NOT | Negates a condition |
BETWEEN | Range check |
IN | Set membership |
LIKE | Pattern matching |
IS NULL | Null check |
% | Wildcard for any sequence |
_ | Wildcard for one character |
Key takeaways:
- The
WHEREclause filters rows. It evaluates a boolean expression for each row. If the expression isTRUE, the row is included. IfFALSEorNULL, the row is excluded . - The
ANDoperator requires both conditions to be true. TheORoperator requires at least one condition to be true. TheNOToperator negates a condition. Parentheses control the order of evaluation . - The
BETWEENoperator tests a range. The range is inclusive.BETWEEN 50 AND 200is equivalent to>= 50 AND <= 200. - The
INoperator tests a set of values.IN ('a', 'b', 'c')is equivalent to= 'a' OR = 'b' OR = 'c'. TheINoperator can also be used with a subquery. - The
LIKEoperator tests a pattern. The%wildcard matches any sequence of characters. The_wildcard matches any single character. TheLIKEoperator is case-sensitive in most databases. - The
IS NULLoperator is the only way to test for null. The= NULLcomparison returnsNULL, which is notTRUE, so the row is not included. UseIS NULLandIS NOT NULLfor null checks. - The
WHEREclause is evaluated before theSELECTclause. The database reads the rows from theFROMtables, filters them with theWHEREclause, then projects the columns specified in theSELECTclause. Only the columns from theFROMtables can be referenced in theWHEREclause.
Remember: The WHERE clause is the filter. It is the difference between “all the data” and “the data I need.” The comparison operators test values. The logical operators combine conditions. The range, set, and pattern operators handle specific cases. The null check is special — it uses IS NULL, not = NULL. The parentheses control the precedence. The WHERE clause is evaluated before the SELECT clause. It is the most important clause after SELECT and FROM.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!