Filtering with WHERE
The Core Theory
The WHERE clause acts as a row-level filter. It evaluates every row in your table against a condition; if the condition is TRUE, the row is included in the result. If it’s FALSE or NULL, it is discarded.
In interviews, you’ll be expected to use these common operators to slice data accurately:
=, !=, <>
Equality and Inequality
Equality and Inequality
>, <, >=, <=
Numerical Comparisons
Numerical Comparisons
IN (…)
Matches any value in a list
Matches any value in a list
BETWEEN a AND b
Inclusive range filtering
Inclusive range filtering
LIKE ‘%str%’
Pattern matching (Wildcards)
Pattern matching (Wildcards)
IS NULL
Checking for empty values
Checking for empty values
Interactive Practice
Q1: Filtering by exact values
Scenario: Find all customers located in ‘New York’.
SELECT * FROM customers WHERE city = ‘New York’;
| id | name | city |
|---|---|---|
| 101 | John Doe | New York |
| 105 | Jane Smith | New York |
Q2: Using the IN operator
Scenario: Get orders for ‘Electronics’ or ‘Home’ categories.
SELECT * FROM orders WHERE category IN (‘Electronics’, ‘Home’);
| order_id | category | amount |
|---|---|---|
| ORD_1 | Electronics | $450 |
| ORD_4 | Home | $120 |
Q3: Filtering NULL values
Scenario: Find leads that don’t have a phone number listed.
SELECT name FROM leads WHERE phone_number IS NULL;
| name |
|---|
| Unknown Prospect |
Q4: Range filtering with BETWEEN
Scenario: Find products priced between $50 and $150.
SELECT product_name, price FROM products WHERE price BETWEEN 50 AND 150;
| product_name | price |
|---|---|
| Ergonomic Chair | 145 |
| Mechanical Keyboard | 89 |
Q5: Logical AND filtering
Scenario: High-value customers (> $1000) from the ‘VIP’ segment.
SELECT name FROM customers WHERE total_spent > 1000 AND segment = ‘VIP’;
| name |
|---|
| Corporate HQ |
