The WHERE clause filters rows before projection and grouping. Correct filters keep queries fast and prevent accidental mass updates when you reuse the same condition in DML.
Comparison operators
SELECT id, name, total
FROM orders
WHERE total >= 50;
SELECT id, name
FROM customers
WHERE name <> 'Ada';Practice: Run in SQLite, DB Fiddle, or Postgres. Create the customers/orders sample from the relational-model lesson if needed.
AND, OR, IN, BETWEEN
SELECT *
FROM orders
WHERE customer_id = 1
AND total BETWEEN 10 AND 100;
SELECT *
FROM orders
WHERE status IN ('shipped', 'pending');Combine predicates carefully—use parentheses when mixing AND and OR to avoid logic bugs.
Pattern matching
SELECT name, email
FROM customers
WHERE email LIKE '%@example.com';
SELECT name
FROM customers
WHERE name LIKE 'A%';LIKE uses % (any length) and _ (one char). Case sensitivity depends on database collation.
Important interview questions and answers
- Q: WHERE vs HAVING?
A: WHERE filters rows before grouping; HAVING filters groups after aggregation. - Q: LIKE performance?
A: Leading wildcards ('%x') often prevent index use—consider full-text search for heavy text queries.
Self-check
- Which operator selects a numeric range inclusively?
- Why put filters in WHERE instead of filtering in application code?
Tip: Always preview UPDATE/DELETE WHERE clauses with the same predicate in SELECT first.
Interview prep
- WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation.
- LIKE wildcard?
% matches any length; _ matches one character.