SQL WHERE Clause

Most questions you ask a database are about some rows, not all of them: the orders from January, the products that are out of stock, the employees in Pune. The WHERE clause filters rows so that only the ones matching a condition reach the result.

WHERE syntax

SELECT column1, column2FROM table_nameWHERE condition;

The condition is checked for every row. Rows where it is true are kept; rows where it is false or unknown (because of NULL) are dropped.

Sample data

Filter on a number

Which products cost less than 1,000?

SELECT product_name, priceFROM productsWHERE price < 1000;

Comparison operators

Products that are out of stock:

SELECT product_id, product_name, stockFROM productsWHERE stock = 0;

Orders that are not delivered yet:

SELECT order_id, customer_id, statusFROM ordersWHERE status <> 'Delivered';

Filter on text

Text values go in single quotes. With MySQL’s default collation the comparison ignores case:

SELECT first_name, last_name, cityFROM employeesWHERE city = 'Pune';

Filter on dates

Write dates as 'YYYY-MM-DD' text. MySQL compares them as dates, so > means “later than”:

SELECT first_name, hire_dateFROM employeesWHERE hire_date >= '2022-01-01'ORDER BY hire_date;

Filter on a calculated value

The condition can use expressions. Which order lines are worth more than 5,000?

SELECT oi.order_id, p.product_name, oi.quantity, p.price,       oi.quantity * p.price AS line_totalFROM order_items oiJOIN products p ON p.product_id = oi.product_idWHERE oi.quantity * p.price > 5000;

WHERE with UPDATE and DELETE

WHERE decides which rows an UPDATE or DELETE touches. Without it, every row changes:

UPDATE productsSET stock = 50WHERE product_id = 3; SELECT product_id, product_name, stock FROM products WHERE product_id = 3;

Common WHERE mistakes

• Comparing with NULL using =. WHERE commission = NULL never matches. Use IS NULL (NULL Values).
• Forgetting quotes around text. WHERE city = Pune makes MySQL look for a column called Pune.
• Quoting numbers. WHERE price < '1000' works, but MySQL has to convert the text first; keep numbers unquoted.
• Using a SELECT alias in WHERE. WHERE line_total > 5000 fails, because WHERE runs before SELECT.

SELECT first_name FROM employees WHERE city = Pune;

ERROR 1054 (42S22): Unknown column 'Pune' in 'where clause'

SELECT first_name, salaryFROM employeesWHERE salary <> 72000;

SELECT order_id, customer_id, order_dateFROM ordersWHERE order_date >= '2025-04-01';

SELECT product_name, priceFROM productsWHERE category = 'Furniture';

WHERE clause FAQ