Filtering Data with WHERE
Until now, our queries have returned every row in a table. In practice, you rarely want to see all the data. You usually want to see a specific subset—like products that are currently out of stock, or users who signed up today.
The WHERE clause is the engine of data filtering in SQL.
How WHERE Works
You place the WHERE clause after the FROM clause, followed by a condition. The database evaluates this condition for every single row in the table. If the condition is TRUE, the row is included in the results. If it’s FALSE, the row is skipped.
SELECT name, price FROM products WHERE category = 'Electronics';(Notice that in SQL, we use single quotes ' for text strings, not double quotes!)
Comparison Operators
SQL provides a suite of operators you can use to build your conditions:
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | WHERE price = 100 |
> | Greater than | WHERE stock_quantity > 0 |
< | Less than | WHERE price < 50 |
>= | Greater than or equal | WHERE price >= 100 |
<= | Less than or equal | WHERE price <= 50 |
<> or != | Not equal to | WHERE category != 'Home' |
Working with Numbers vs Text
When checking numeric columns (like price or stock_quantity), you just write the number.
When checking text columns (like category or name), you must wrap the text in single quotes ('Office'). Furthermore, text comparisons in SQL are often case-sensitive depending on your database engine. Comparing category = 'office' might not match 'Office'.
[!WARNING] The order of SQL clauses is strictly enforced.
WHEREmust always come afterFROMand beforeORDER BYorLIMIT.✅ Correct:
SELECT * FROM products WHERE price > 100 ORDER BY price LIMIT 5;❌ Incorrect:SELECT * FROM products ORDER BY price WHERE price > 100;
Try writing a query in the lab to help the inventory team find products that are running low on stock!