MySQL WHERE Clause

MySQL WHERE Clause: Filtering Query Results

The MySQL WHERE clause filters table rows so your query only returns, updates, or deletes records matching a logical condition. Without WHERE, a query scans and affects every row in the table.

Operators Supported Inside WHERE

You can build precise filters by combining standard SQL comparison and range operators. Each predicate evaluates to true, false, or unknown (NULL) for every candidate row:

• Comparison: Equal (=), not equal (!=), greater than, less than, greater than or equal, and less than or equal.
• Ranges and Lists: Match continuous numeric or date bounds with BETWEEN, or test list and subquery membership with IN and NOT IN.
• Pattern Matching: Match partial strings with LIKE wildcards or validate text formats using REGEXP.
• Missing Values: Test for missing data safely with IS NULL and IS NOT NULL.

Practical SQL Example

This query filters demo_products to return active items priced above fifty dollars and sorts matching rows from lowest to highest price:

SELECT product_name, price, category
FROM demo_products
WHERE price > 50.00
  AND status = 'active'
ORDER BY price ASC;

Common Mistakes and Best Practices

• Keep Indexed Columns Clean: Wrapping a column in a function like WHERE YEAR(created_at) = 2026 disables B-tree indexes and causes a full table scan. Rewrite it as a sargable date range with explicit created_at start and end bounds.
• Never Compare NULL with =: Writing WHERE deleted_at = NULL always evaluates to unknown and returns zero rows. Always write WHERE deleted_at IS NULL or WHERE deleted_at IS NOT NULL.
• Match Data Types: Comparing a VARCHAR column against an unquoted integer forces row-by-row type casting and skips indexes. Always quote string literals and keep numeric literals unquoted.

Frequently Asked Questions

Can I use WHERE inside UPDATE and DELETE statements?

Yes, always include a WHERE clause in UPDATE and DELETE queries unless you intentionally want to modify every row.

What is the execution order between FROM, WHERE, and SELECT?

MySQL resolves FROM first, filters rows with WHERE, and evaluates column expressions in SELECT afterward.

Why can't I reference a SELECT alias inside WHERE?

Because WHERE runs before SELECT evaluates column aliases, you must repeat the original column or expression in WHERE.