WHERE Clause in SQL: Filtering with =, !=, >, <, >=, <=

Retrieving every row in a table is rarely useful in real applications — you almost always want only the rows that match a specific condition: orders over a certain amount, customers in a specific city, products below a price threshold. The WHERE clause is SQL's primary filtering mechanism, and comparison operators are its most fundamental building block.

What Is the WHERE Clause?

WHERE is a clause added to SELECT, UPDATE, and DELETE statements that filters rows based on a specified condition. Only rows for which the condition evaluates to true are included in the result (for SELECT) or affected (for UPDATE/DELETE) — every other row is simply skipped.

What You'll Learn

• Filter query results using the six basic comparison operators.
• Apply WHERE conditions across numeric, text, and date columns.
• Understand the difference between = and != (or <>) for equality checks.
• Recognize how WHERE interacts with UPDATE and DELETE, not just SELECT.

Key Terms to Know

• WHERE clause: A clause that filters rows based on a specified condition.
• Comparison operator: A symbol like =, !=, >, <, >=, or <= used to compare a column's value against another value.
• Condition: A boolean expression that evaluates to true or false for each row.
• Predicate: Another term for the boolean condition used inside a WHERE clause.

The Six Basic Comparison Operators

= checks for equality, such as WHERE category = 'electronics'. != or <> checks for inequality (both are valid in MySQL and behave identically), such as WHERE status != 'cancelled'. > and < check strictly greater than and less than, such as WHERE price > 1000. >= and <= check greater-than-or-equal and less-than-or-equal, such as WHERE stock_qty <= 10 for a low-stock alert.

These operators work consistently across numeric, date, and even text columns (where comparison follows alphabetical/collation order), making WHERE flexible across virtually any column type.

WHERE with Numeric, Text, and Date Columns

Numeric comparisons behave intuitively: WHERE price > 500 returns rows where price exceeds 500. Text comparisons follow the column's collation rules — WHERE city = 'Mumbai' typically matches case-insensitively under common collations like utf8mb4_unicode_ci, though this depends on the specific collation in use.

Date comparisons work the same way as numbers once a column is properly typed as DATE or DATETIME: WHERE order_date >= '2026-01-01' correctly returns rows from that date onward, which is exactly why storing dates as DATE/DATETIME rather than VARCHAR (covered back in the data types lesson) matters so much.

WHERE Beyond SELECT: UPDATE and DELETE

WHERE isn't exclusive to SELECT — it's equally critical in UPDATE table_name SET column = value WHERE condition; and DELETE FROM table_name WHERE condition;, where it determines exactly which rows get modified or removed. Running UPDATE or DELETE without a WHERE clause affects every single row in the table, which is one of the most common and dangerous mistakes in SQL.

Visual Summary

Picture a long line of people (rows) waiting to enter a venue, with a bouncer (WHERE clause) checking each person against a specific rule, like 'age >= 21'. Only people who pass the check get let through into the result set; everyone else is turned away and never appears on the other side.

Comparison Operators Reference

Operator	Meaning	Example
=	Equal to	WHERE category = 'electronics'
!= or <>	Not equal to	WHERE status != 'cancelled'
>	Greater than	WHERE price > 1000
<	Less than	WHERE stock_qty < 5
>=	Greater than or equal to	WHERE rating >= 4
<=	Less than or equal to	WHERE discount <= 20

SQL Example

-- Products priced above 1000
SELECT product_name, price FROM products WHERE price > 1000;

-- Orders that are not cancelled
SELECT order_id, status FROM orders WHERE status != 'cancelled';

-- Low-stock alert: 10 or fewer units remaining
SELECT product_name, stock_qty FROM products WHERE stock_qty <= 10;

-- Using WHERE with UPDATE: apply a discount only to a specific category
UPDATE products
SET price = price * 0.9
WHERE category = 'clearance';

Each SELECT example demonstrates a different operator solving a realistic filtering need: price filtering, status exclusion, and a low-stock threshold check. The final UPDATE example shows WHERE controlling exactly which rows get a price discount applied — without it, every product in the entire table would have been discounted by mistake.

Real-World Examples

• E-commerce sites use WHERE price BETWEEN ... style filters (covered fully in a later lesson) built on these same comparison operators for category and price browsing.
• Inventory systems use WHERE stock_qty <= reorder_threshold to trigger automatic restocking alerts.
• Customer support tools use WHERE status != 'resolved' to surface only open tickets that still need attention.
• Finance teams use WHERE transaction_date >= '2026-01-01' to scope reports to the current fiscal year.

Best Practices and Pro Tips

• Always double-check a WHERE clause on UPDATE and DELETE statements before running them in production — consider running the equivalent SELECT with the same WHERE clause first to preview exactly which rows will be affected.
• Use != or <> consistently within a single codebase rather than mixing both, purely for team readability, since they behave identically in MySQL.
• Store dates as proper DATE/DATETIME types so WHERE comparisons on them behave chronologically correct, rather than comparing them as misleading text strings.

Common Mistakes to Avoid

• Running UPDATE or DELETE without a WHERE clause, accidentally affecting every row in the table.
• Comparing a date stored as VARCHAR using > or <, which compares the text alphabetically rather than chronologically, producing wrong results.
• Forgetting that string comparisons can be case-sensitive or case-insensitive depending on the column's collation, leading to unexpected matches or misses.
• Using = to compare against NULL, which always evaluates to unknown/false rather than true — NULL requires IS NULL, covered in a later lesson.

Interview Questions

Q1. What is the purpose of the WHERE clause in SQL?

WHERE filters rows so that only those satisfying a specified condition are included in a SELECT result, or affected by an UPDATE or DELETE statement. Rows that don't satisfy the condition are excluded entirely.

Q2. What happens if you run an UPDATE statement without a WHERE clause?

Every single row in the table gets updated according to the SET clause, since there's no condition to limit which rows are affected. This is one of the most common and costly mistakes in SQL.

Q3. Why doesn't WHERE column = NULL work as expected?

Comparing anything to NULL using = always evaluates to unknown rather than true, even when the column actually is NULL. The correct way to check for NULL is WHERE column IS NULL, covered in a later lesson.

Q4. Why does comparing dates stored as VARCHAR with > produce unreliable results?

Text comparison happens alphabetically/lexically rather than chronologically, so a VARCHAR date like '2026-2-1' could compare incorrectly against '2026-10-1'. Storing dates as DATE/DATETIME avoids this entirely.

Practice MCQs

1. Which operator checks that a column's value is NOT equal to a given value?

1. =
2. <=
3. != (or <>)
4. >=

Answer: C. != (or <>)

Explanation: Both != and <> check for inequality in MySQL and behave identically.

2. What is the main risk of running DELETE without a WHERE clause?

1. The statement fails immediately
2. Only the first row is deleted
3. Every row in the table is deleted
4. MySQL automatically adds a WHERE clause

Answer: C. Every row in the table is deleted

Explanation: Without a WHERE clause, DELETE removes every row in the table, since there's no condition limiting which rows are affected.

3. Which WHERE condition correctly filters for products with stock 10 or fewer?

1. WHERE stock_qty < 10
2. WHERE stock_qty <= 10
3. WHERE stock_qty != 10
4. WHERE stock_qty = 10

Answer: B. WHERE stock_qty <= 10

Explanation: <= includes values equal to 10 as well as those below it, correctly capturing '10 or fewer'.

Quick Revision Points

• WHERE filters rows in SELECT, UPDATE, and DELETE statements based on a condition.
• The six comparison operators: =, != (or <>), >, <, >=, <=.
• WHERE column = NULL never works as expected; use IS NULL instead.
• Omitting WHERE on UPDATE/DELETE affects every row in the table.

Conclusion

• WHERE is the single most important clause for controlling exactly which rows a query touches.
• The same six comparison operators apply consistently across numeric, text, and date columns.
• WHERE's role in UPDATE and DELETE makes it as much a safety mechanism as a filtering tool.

The WHERE clause is SQL's fundamental filtering tool, using comparison operators like =, !=, >, <, >=, and <= to narrow down exactly which rows a SELECT, UPDATE, or DELETE statement should touch. Used correctly, it turns a table-wide operation into a precise, targeted one; used carelessly — especially when omitted entirely on UPDATE or DELETE — it can silently affect far more data than intended. The next lesson builds on this foundation with logical operators (AND, OR, NOT) for combining multiple conditions together.