Filter rows with WHERE
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A table can hold lakhs of rows, and a screen usually needs only a few of them. A WHERE clause states a condition, and MySQL returns only the rows for which that condition is true.
Comparison operators
A WHERE clause holds a condition, a test that is true or false for each row. MySQL keeps only the rows for which the condition is true and drops all the others.
A table can hold thousands of rows, and a screen needs only a few. Filter inside the database: the first query keeps products under 1000, the second keeps orders that are not delivered yet.
Example 1 · Cheap products and orders not yet delivered
SELECT product_id, name, priceFROM productsWHERE price < 1000ORDER BY product_id;
SELECT order_id, customer_id, statusFROM ordersWHERE status <> 'DELIVERED'ORDER BY order_id;+------------+---------------------+--------+ | product_id | name | price | +------------+---------------------+--------+ | 8 | Logitech M331 Mouse | 849.00 | | 9 | Clean Code | 499.00 | | 10 | Hands-On Python | 650.00 | | 11 | Atomic Habits | 399.00 | | 12 | Cotton Kurta | 899.00 | | 14 | Rubik's Cube | 299.00 | | 15 | Gift Card Rs. 500 | 500.00 | +------------+---------------------+--------+ +----------+-------------+-----------+ | order_id | customer_id | status | +----------+-------------+-----------+ | 4 | 3 | CANCELLED | | 7 | 2 | SHIPPED | | 10 | 1 | SHIPPED | | 11 | 8 | PLACED | | 13 | 9 | PLACED | | 14 | 5 | CANCELLED | | 15 | 2 | PLACED | +----------+-------------+-----------+
What this code does
- The first query tests
price < 1000on all 15 products and keeps the 7 where it is true.WHEREgoes afterFROMand beforeORDER BY. <>means "not equal", so the second query returns every order that is placed, shipped or cancelled.!=gives the same result.
Example 2 · WHERE cannot use a SELECT alias
-- Works: repeat the expression in WHERE.SELECT name, price * stock AS stock_valueFROM productsWHERE price * stock > 500000ORDER BY product_id;
-- Fails: the alias does not exist yet when WHERE runs.SELECT name, price * stock AS stock_valueFROM productsWHERE stock_value > 500000;+-----------------------+-------------+ | name | stock_value | +-----------------------+-------------+ | Redmi Note 13 | 719960.00 | | iPhone 15 | 559200.00 | | Lenovo IdeaPad Slim 3 | 515880.00 | +-----------------------+-------------+ ERROR 1054 (42S22): Unknown column 'stock_value' in 'where clause'
What this code does
- MySQL filters rows with
WHEREbefore it builds theSELECTlist, so the aliasstock_valueis unknown at that point. Repeating the expression works. - The second query fails with error 1054,
Unknown column 'stock_value' in 'where clause'. This order of steps is a favourite interview question; the Aggregates & Grouping module covers the full order of a query.
The comparison operators are = (equal), <> or != (not equal), <, >, <= and >=. SQL uses a single = for "equal"; there is no ==.
WHERE price < 1000: cheaper than 1000.WHERE stock = 0: out of stock.WHERE status <> 'DELIVERED': every status except DELIVERED.- A condition that matches no row is not an error. The result is just empty, and the mysql client prints
Empty set.
AND, OR and NOT
AND, OR and NOT join simple conditions into a bigger one. a AND b is true only when both are true, a OR b when at least one is true, and NOT a flips true to false.
Real questions have more than one part: "Cardiology patients aged 60 or more", "patients of Cardiology or Orthopaedics". Each part is one comparison; the logical operators join them.
Example 3 · AND, OR and NOT in a hospital OPD
DROP TABLE IF EXISTS opd_visits;CREATE TABLE opd_visits ( visit_id INT PRIMARY KEY, patient VARCHAR(40) NOT NULL, department VARCHAR(20) NOT NULL, age INT NOT NULL, fee INT NOT NULL, visit_date DATE NOT NULL);INSERT INTO opd_visits VALUES (1, 'Ramesh', 'Cardiology', 64, 800, '2025-06-02'), (2, 'Sunita', 'Orthopaedics', 45, 600, '2025-06-02'), (3, 'Iqbal', 'Cardiology', 52, 800, '2025-06-03'), (4, 'Lata', 'General', 71, 300, '2025-06-03'), (5, 'Vijay', 'Orthopaedics', 67, 600, '2025-06-04'), (6, 'Meera', 'General', 29, 300, '2025-06-04');
-- Cardiology patients aged 60 or moreSELECT patient, department, ageFROM opd_visitsWHERE department = 'Cardiology' AND age >= 60ORDER BY visit_id;
-- Patients of Cardiology or OrthopaedicsSELECT patient, departmentFROM opd_visitsWHERE department = 'Cardiology' OR department = 'Orthopaedics'ORDER BY visit_id;
-- Everyone except GeneralSELECT patient, departmentFROM opd_visitsWHERE NOT department = 'General'ORDER BY visit_id;+---------+------------+-----+ | patient | department | age | +---------+------------+-----+ | Ramesh | Cardiology | 64 | +---------+------------+-----+ +---------+--------------+ | patient | department | +---------+--------------+ | Ramesh | Cardiology | | Sunita | Orthopaedics | | Iqbal | Cardiology | | Vijay | Orthopaedics | +---------+--------------+ +---------+--------------+ | patient | department | +---------+--------------+ | Ramesh | Cardiology | | Sunita | Orthopaedics | | Iqbal | Cardiology | | Vijay | Orthopaedics | +---------+--------------+
What this code does
- With
AND, Iqbal is left out: he is in Cardiology but 52, so only one of the two tests is true. - With
OR, a row needs only one true test, so four patients match.NOTkeeps the same four rows here, because those are exactly the patients outside General.
ANDnarrows the result: every extra condition can only remove rows.ORwidens it: every extra condition can only add rows.NOT status = 'CANCELLED'means the same asstatus <> 'CANCELLED'. Use whichever reads more clearly.- A common slip:
city = 'Pune' AND city = 'Delhi'returns nothing, because one row cannot have two cities. You meantOR.
Brackets and operator precedence
Operator precedence is the order in which MySQL applies operators when there are no brackets. NOT comes first, then AND, then OR, just as * comes before + in arithmetic.
One missing bracket can return the wrong rows without any error. The first query mixes OR with AND and returns too much; the second adds brackets and returns what was meant.
Example 4 · The AND-before-OR bug and its fix
-- Wrong: read as PLACED OR (SHIPPED AND customer 2)SELECT order_id, customer_id, statusFROM ordersWHERE status = 'PLACED' OR status = 'SHIPPED' AND customer_id = 2ORDER BY order_id;
-- Right: brackets group the OR firstSELECT order_id, customer_id, statusFROM ordersWHERE (status = 'PLACED' OR status = 'SHIPPED') AND customer_id = 2ORDER BY order_id;+----------+-------------+---------+ | order_id | customer_id | status | +----------+-------------+---------+ | 7 | 2 | SHIPPED | | 11 | 8 | PLACED | | 13 | 9 | PLACED | | 15 | 2 | PLACED | +----------+-------------+---------+ +----------+-------------+---------+ | order_id | customer_id | status | +----------+-------------+---------+ | 7 | 2 | SHIPPED | | 15 | 2 | PLACED | +----------+-------------+---------+
What this code does
- The first query also returns orders 11 and 13, which belong to customers 8 and 9.
ANDwas applied first, so the customer test only guarded the SHIPPED part. - With brackets, both statuses are checked first and then the customer, so only customer 2's open orders, 7 and 15, remain.
status = 'PLACED' OR status = 'SHIPPED' AND customer_id = 2 is read as PLACED OR (SHIPPED AND customer_id = 2), so it returns every placed order of every customer. Brackets are always worked out first.
Comparing text and dates
A collation is the rule set that decides how MySQL compares and sorts text. The default, utf8mb4_0900_ai_ci, ends in ci, which means case-insensitive, so 'chennai' matches Chennai.
Text and dates go inside single quotes, and dates are written year first. The queries below find customers by city in any case, students whose name sorts before D, and the orders of one month.
Example 5 · Text in any case and a date range
SELECT name, cityFROM customersWHERE city = 'chennai'ORDER BY customer_id;
SELECT nameFROM studentsWHERE name < 'D'ORDER BY student_id;
SELECT order_id, order_dateFROM ordersWHERE order_date >= '2025-04-01' AND order_date < '2025-05-01'ORDER BY order_id;+------------+---------+ | name | city | +------------+---------+ | Ravi Kumar | Chennai | | Meena Iyer | Chennai | +------------+---------+ +---------------+ | name | +---------------+ | Aditya Joshi | | Bhavana Reddy | | Chirag Patel | +---------------+ +----------+------------+ | order_id | order_date | +----------+------------+ | 9 | 2025-04-04 | | 10 | 2025-04-12 | | 11 | 2025-04-25 | +----------+------------+
What this code does
'chennai'in small letters still finds both Chennai customers, because the default collation ignores case. Names that sort before'D'are those starting with A, B or C.- Dates in
YYYY-MM-DDform compare in time order, so>= '2025-04-01' AND < '2025-05-01'returns every April order, from the 1st to the 30th.
Example 6 · Mistake: a date written as DD-MM-YYYY
-- Meant: orders from 1 April 2025 onwards.SELECT order_id, order_dateFROM ordersWHERE order_date >= '01-04-2025';ERROR 1525 (HY000): Incorrect DATE value: '01-04-2025'
What this code does
- MySQL tries to read
'01-04-2025'as year 01, month 04, day 2025, and stops with error 1525,Incorrect DATE value. - Write the year first:
WHERE order_date >= '2025-04-01'.
Without quotes MySQL reads a word as a column name, so city = Pune fails with ERROR 1054 (42S22): Unknown column 'Pune' in 'where clause'. Text is compared in dictionary order, so name < 'D' means names that sort before D.
- Numbers need no quotes:
price < 1000, notprice < '1000'. - For a whole month, use
order_date >= '2025-04-01' AND order_date < '2025-05-01'. It stays correct for months of any length.
Fix the error
Fix the broken filter
Read the error, then pick the line that fixes it.
Round 1 of 2
Pick the line that replaces line 3 so the query works.
Error
ERROR 1054 (42S22): Unknown column 'Pune' in 'where clause'
Next step
Check your understanding
11 quick questions