WHERE
WHERE keeps only the rows where a condition is true. It goes after FROM.
Syntax
Order of execution: FROM → WHERE → SELECT → ORDER BY
WHERE runs before SELECT, so it filters the table's rows before any column is built. That's
why WHERE can't use an alias you define in SELECT: repeat the expression instead.
CodeSELECT column1, column2, ... FROM table_name WHERE condition;
| Operator | Meaning | Example |
|---|---|---|
= <> != | equal, not equal (<> and != mean the same) | city = 'Delhi' |
< > <= >= | compare | price >= 20000 |
AND OR NOT | combine conditions | city = 'Pune' AND loyalty_points > 500 |
BETWEEN a AND b | from a to b, both included | price BETWEEN 2000 AND 3000 |
IN (...) NOT IN (...) | in a list, or not | city IN ('Pune', 'Kochi') |
LIKE ILIKE | text pattern: % is any text, _ is one character; ILIKE is PostgreSQL's | product_name LIKE '%Set%' |
IS NULL IS NOT NULL | value missing, or present | phone IS NULL |
Examples
Comparison
Keep rows where a value equals, differs from, or is above or below something.
CodeSELECT product_name, stock FROM products WHERE stock = 0; SELECT product_name, price FROM products WHERE price >= 20000; SELECT full_name, city FROM customers WHERE city <> 'Delhi'; SELECT full_name, city FROM customers WHERE city != 'Delhi'; -- the same as <>
AND, OR
Filter on more than one condition at once.
Code-- Both conditions must be true SELECT full_name, city, loyalty_points FROM customers WHERE city <> 'Delhi' AND loyalty_points > 1500; -- At least one condition must be true SELECT full_name, city, loyalty_points FROM customers WHERE city = 'Mumbai' OR loyalty_points >= 2000;
Mixing AND and OR
Combine "either of these" with "and also this" without getting the wrong rows.
Code-- Wrong: AND runs before OR, so this reads as Pune OR (Kochi AND returned) SELECT order_id, shipping_city, status FROM orders WHERE shipping_city = 'Pune' OR shipping_city = 'Kochi' AND status = 'returned'; -- Right: parentheses group the OR first SELECT order_id, shipping_city, status FROM orders WHERE (shipping_city = 'Pune' OR shipping_city = 'Kochi') AND status = 'returned';
BETWEEN, IN
Match a range of values, or any value from a list, without writing a chain of conditions.
CodeSELECT product_name, price FROM products WHERE price BETWEEN 2000 AND 3000; -- 2000 and 3000 included SELECT full_name, city FROM customers WHERE city IN ('Pune', 'Kochi'); SELECT full_name, city FROM customers WHERE city NOT IN ('Delhi', 'Mumbai');
LIKE, ILIKE
Find text when you only know part of it, such as a word in a product name. In PostgreSQL and
Oracle, LIKE is case-sensitive; MySQL and SQL Server usually ignore case. ILIKE exists only in
PostgreSQL; elsewhere, compare LOWER(column) with a lowercase pattern.
CodeSELECT product_name FROM products WHERE product_name LIKE '%Set%'; -- contains "Set" SELECT product_name FROM products WHERE product_name LIKE '%set%'; -- no match: PostgreSQL's LIKE is case-sensitive SELECT product_name FROM products WHERE product_name ILIKE '%book%'; -- ILIKE ignores case SELECT full_name FROM customers WHERE full_name LIKE '% K%'; -- surname starts with K
NULL
Find rows where a value is missing, or make sure they aren't dropped by mistake.
CodeSELECT full_name FROM customers WHERE phone = NULL; -- wrong: never matches anything SELECT full_name FROM customers WHERE phone IS NULL; -- no phone number SELECT full_name FROM customers WHERE phone IS NOT NULL; -- has a phone number SELECT product_name, rating FROM products WHERE rating < 4; -- skips unrated products SELECT product_name, rating FROM products WHERE rating < 4 OR rating IS NULL; -- includes them
Remember
- Text and dates go in single quotes. In PostgreSQL text must match exactly:
'delhi'is not'Delhi'(MySQL and SQL Server usually ignore case). - When you mix
ANDandOR, use parentheses. BETWEENincludes both ends.= NULLnever matches, so useIS NULL. Comparisons such asrating < 4skip NULL rows too.
Practice
- Well-Rated Products From ₹999 to ₹1,999 (Easy)
- Search Results for "mat" (Easy)
- Daily Needs Under ₹1,000 (Easy)
- Products to Review (Easy)