MySQLWHERE Clause

WHERE Clause in MySQL

The WHERE clause filters rows before they are returned to the client (SELECT), updated (UPDATE), or deleted (DELETE). It is evaluated against every candidate row, and only rows where the condition evaluates to TRUE are included in the result. Rows evaluating to FALSE or NULL are excluded.

WHERE Clause Basics and Query Position

The WHERE clause appears after the FROM clause (and JOIN clauses) but before GROUP BY, HAVING, ORDER BY, and LIMIT. The logical execution order is:

  1. FROM / JOIN — identify the source rows
  2. WHERE — filter individual rows
  3. GROUP BY — group filtered rows
  4. HAVING — filter groups
  5. SELECT — project columns
  6. ORDER BY — sort the result
  7. LIMIT — restrict row count

SQL
SELECT department_id, AVG(salary)
FROM employees
WHERE hire_date > '2020-01-01'    -- runs before GROUP BY
GROUP BY department_id
HAVING AVG(salary) > 60000        -- runs after GROUP BY
ORDER BY AVG(salary) DESC
LIMIT 10;
Note
WHERE filters rows; HAVING filters groups. You cannot use a WHERE clause to filter on an aggregate result — that requires HAVING.
All Comparison Operators

SQL
-- Equality
SELECT * FROM employees WHERE department_id = 3;

-- Not equal (both forms are equivalent)
SELECT * FROM products WHERE category_id != 5;
SELECT * FROM products WHERE category_id <> 5;

-- Less than, less than or equal
SELECT * FROM orders WHERE total < 100;
SELECT * FROM orders WHERE total <= 99.99;

-- Greater than, greater than or equal
SELECT * FROM orders WHERE total > 500;
SELECT * FROM orders WHERE total >= 1000;

-- NULL-safe equals operator (<=>)
-- Returns TRUE when both sides are NULL, unlike regular =
SELECT * FROM employees WHERE manager_id <=> NULL;
-- Equivalent to: WHERE manager_id IS NULL
NULL Comparisons: IS NULL, IS NOT NULL, and <=>

NULL represents an unknown value. Any comparison with NULL using =, !=, <, or > returns NULL (not TRUE or FALSE), so the row is silently excluded. Always use IS NULL or IS NOT NULL to test for NULL values.

SQL
-- WRONG: always returns 0 rows (NULL = NULL evaluates to NULL, not TRUE)
SELECT * FROM employees WHERE manager_id = NULL;

-- CORRECT: IS NULL
SELECT * FROM employees WHERE manager_id IS NULL;

-- CORRECT: IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NOT NULL;

-- NULL-safe equals: returns TRUE when both sides are NULL
-- Useful in JOIN conditions or when comparing two nullable columns
SELECT * FROM products p1
JOIN products p2 ON p1.supplier_id <=> p2.supplier_id;

-- COALESCE: treat NULL as a default value in WHERE
SELECT * FROM users WHERE COALESCE(notes, '') = '';
-- Matches rows where notes is NULL OR notes is an empty string
Warning
The most common NULL bug: WHERE col != 'value' silently excludes NULL rows because NULL != 'value' evaluates to NULL, not TRUE. Add OR col IS NULL if you want to include NULL rows in your results.
AND, OR, NOT — Operator Precedence

SQL
-- AND: both conditions must be TRUE
SELECT * FROM employees
WHERE department_id = 3
  AND salary > 60000;

-- OR: either condition may be TRUE
SELECT * FROM products
WHERE category_id = 1
   OR category_id = 2;

-- NOT: negate a condition
SELECT * FROM users WHERE NOT is_suspended;
SELECT * FROM orders WHERE NOT status = 'cancelled';  -- same as status != 'cancelled'

-- Combining AND and OR
SELECT * FROM orders
WHERE status = 'pending'
   OR (status = 'processing' AND priority = 'high');
Warning
AND has higher precedence than OR. Without parentheses, complex conditions are parsed in an order that may surprise you. Always use parentheses to make your intent explicit.

SQL
-- Ambiguous: parsed as (department_id=1 AND salary>50000) OR department_id=2
-- This returns ALL employees in dept 2, even those with low salaries
SELECT * FROM employees
WHERE department_id = 1
  AND salary > 50000
   OR department_id = 2;

-- Clear intent with parentheses: only high earners in dept 1 OR dept 2
SELECT * FROM employees
WHERE (department_id = 1 OR department_id = 2)
  AND salary > 50000;
Short-Circuit Evaluation

MySQL evaluates WHERE conditions left to right and may stop early (short-circuit) once the overall truth value is determined:

  • In an AND chain, as soon as one condition is FALSE, the rest are skipped.
  • In an OR chain, as soon as one condition is TRUE, the rest are skipped.

Place cheaper or more selective conditions first in AND chains to let MySQL skip expensive function calls or subqueries for rows that fail the early checks.

SQL
-- Place cheap indexed check first, expensive function call second
SELECT * FROM orders
WHERE status = 'active'              -- indexed, cheap
  AND JSON_CONTAINS(metadata, '"premium"', '$.tags');  -- expensive JSON parse

-- If status = 'active' is false, the JSON_CONTAINS is never evaluated for that row
SARGable vs Non-SARGable Predicates

A SARGable predicate (Search ARGument able) is one that MySQL can use to seek into an index. A non-SARGable predicate wraps the indexed column in a function or expression, preventing index use and forcing a full table scan.

SQL
-- NON-SARGable (bad): YEAR() wraps the column, index cannot be used
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL   rows: 500000   (full scan)

-- SARGable (good): range condition on the column itself
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';
-- type: range   key: idx_created_at   rows: ~50000 (index range scan)

-- NON-SARGable: function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- type: ALL (full scan)

-- SARGable fix: rely on the column's _ci collation (case-insensitive by default)
SELECT * FROM users WHERE email = 'alice@example.com';
-- type: ref (index used)

-- NON-SARGable: arithmetic on indexed column
SELECT * FROM products WHERE price * 1.1 > 100;
-- type: ALL

-- SARGable fix: move the arithmetic to the other side
SELECT * FROM products WHERE price > 100 / 1.1;
-- type: range (index used)
Tip
The rule is simple: never wrap an indexed column in a function or expression on the left side of a WHERE condition. Move any transformation to the right-hand side instead.
WHERE with BETWEEN, IN, LIKE, REGEXP

SQL
-- BETWEEN: inclusive range (>= AND <=)
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59';

-- IN: match any value from a list
SELECT * FROM employees WHERE department_id IN (1, 3, 7);
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- NOT IN: exclude values (careful if list may contain NULL!)
SELECT * FROM orders WHERE status NOT IN ('delivered', 'cancelled');

-- LIKE: pattern matching (prefix = index-friendly, leading % = slow)
SELECT * FROM customers WHERE last_name LIKE 'Smi%';  -- uses index
SELECT * FROM articles WHERE title LIKE '%tutorial%'; -- full scan

-- REGEXP: powerful pattern matching (always full scan)
SELECT * FROM users WHERE email REGEXP '^[a-z0-9]+@[a-z0-9]+\.com$';
WHERE with Subqueries: EXISTS vs IN vs JOIN

SQL
-- EXISTS: returns TRUE if the subquery produces at least one row
-- Short-circuits on the first match -- efficient for large subquery results
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
);

-- NOT EXISTS: customers who have never ordered
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- IN with a subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = 1
);

-- Equivalent JOIN (often most readable and performant)
SELECT DISTINCT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

-- Scalar comparison
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
Tip
Use EXISTS instead of IN when the subquery may return a large number of rows. EXISTS stops at the first match; IN evaluates the entire subquery. Also, NOT IN behaves unexpectedly if the subquery returns any NULL — prefer NOT EXISTS in that case.
WHERE in UPDATE and DELETE Statements

SQL
-- UPDATE with WHERE
UPDATE employees
SET salary = salary * 1.05
WHERE performance_rating = 'excellent'
  AND hire_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- DELETE with WHERE
DELETE FROM logs
WHERE level = 'debug'
  AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- The same SARGability rules apply in UPDATE/DELETE:
-- wrapping indexed columns in functions prevents index use
-- and turns an O(log n) delete into an O(n) full-table scan
Warning
Omitting WHERE from UPDATE or DELETE affects every row in the table. Enable sql_safe_updates = 1 to make MySQL reject such statements unless they include a key-column filter.
WHERE with Window Functions (Limitation)

Window functions (OVER) are computed after the WHERE clause. This means you cannot filter directly on a window function result in the WHERE clause — you must wrap the query in a subquery or CTE first.

SQL
-- This FAILS: window functions are not allowed in WHERE
SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk <= 3;  -- ERROR: Unknown column 'rnk' in 'where clause'

-- Fix: wrap in a subquery and filter in the outer WHERE
SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

-- Or use a CTE (MySQL 8.0+)
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;
Index Usage in WHERE — Common Scenarios

WHERE Pattern

Index Used?

Scan Type

Notes

WHERE id = 5

Yes

const / eq_ref

Single PK or unique lookup — best possible

WHERE price > 100

Yes

range

Range on indexed column

WHERE status IN (1,2,3)

Yes

range

IN on indexed column treated as range

WHERE YEAR(created_at) = 2024

No

ALL

Function wraps column — use range instead

WHERE email LIKE "ali%"

Yes

range

Leading prefix — index usable

WHERE email LIKE "%ali%"

No

ALL

Leading wildcard — full scan

WHERE col IS NULL

Yes (if indexed)

ref

NULLs are indexed in MySQL

WHERE a = 1 AND b = 2

Yes (if composite index)

ref

Composite index on (a, b) works well

WHERE b = 2 (no a filter)

No (composite only)

ALL

Composite index requires leading column

Practical Filtering Patterns for Report Queries

SQL
-- Date range report (SARGable)
SELECT DATE(created_at) AS day, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01'
GROUP BY DATE(created_at)
ORDER BY day;

-- Status + date combo (composite index on status, created_at recommended)
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- Paginated filtered list (cursor-based for performance)
SELECT id, name, email, created_at
FROM users
WHERE is_active = 1
  AND created_at < '2024-06-15'     -- cursor from last page
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- Nullable column with a safe fallback
SELECT * FROM employees
WHERE COALESCE(department_id, 0) = 0;  -- NULL department = unassigned
-- Note: COALESCE wraps the column, preventing index use
-- SARGable alternative:
SELECT * FROM employees WHERE department_id IS NULL;
The NULL Trap in NOT IN

This is one of the most surprising NULL-related bugs. If a subquery used with NOT IN returns even a single NULL, the entire NOT IN condition evaluates to NULL — and no rows are returned.

SQL
-- Demonstrate the NOT IN / NULL trap
CREATE TABLE a (x INT);
CREATE TABLE b (x INT);
INSERT INTO a VALUES (1), (2), (3);
INSERT INTO b VALUES (1), (NULL);   -- b contains a NULL!

-- Expected: return rows from a not in b (should return 2 and 3)
-- Actual: returns 0 rows because of the NULL in b
SELECT x FROM a WHERE x NOT IN (SELECT x FROM b);

-- Explanation:
-- NOT IN (1, NULL) evaluates as: x != 1 AND x != NULL
-- x != NULL is always NULL (unknown), so the whole condition is NULL
-- Result: no rows pass the filter

-- Fix: use NOT EXISTS instead
SELECT x FROM a
WHERE NOT EXISTS (
  SELECT 1 FROM b WHERE b.x = a.x
);
-- Returns: 2, 3  (correct, NULLs are handled safely)
Warning
Always use NOT EXISTS instead of NOT IN when the subquery could return NULL values. NOT IN with a NULL-containing list silently returns zero rows, which is a very hard bug to spot.
WHERE with Composite Indexes

A composite index on (col_a, col_b, col_c) can be used to satisfy WHERE conditions in a specific way: MySQL reads the index from left to right and can use a prefix of the index.

SQL
ALTER TABLE orders ADD INDEX idx_composite (status, customer_id, created_at);

-- Uses full composite index (leftmost prefix matches)
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
  AND customer_id = 42
  AND created_at > '2024-01-01';
-- key: idx_composite   type: range

-- Uses partial composite index (status only)
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- key: idx_composite   type: ref   (uses leading column only -- still benefits)

-- Uses partial (status + customer_id)
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND customer_id = 42;
-- key: idx_composite   type: ref

-- Does NOT use the composite index (skips the leading column)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- key: NULL   type: ALL   (index not usable without the leading column)

-- Rule: the index can only be used if the WHERE includes the leftmost columns
Tip
Design composite indexes with the most selective column on the left, followed by columns that appear in equality conditions (=), then range conditions (<, >, BETWEEN). This layout maximises the portion of the index that can be used.
WHERE in HAVING vs WHERE — When to Use Each

SQL
-- WHERE filters individual rows BEFORE aggregation
-- HAVING filters groups AFTER aggregation

-- Correct: filter on a non-aggregate column in WHERE
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE hire_date > '2020-01-01'   -- filter rows before counting
GROUP BY department_id;

-- Correct: filter on an aggregate result in HAVING
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;             -- filter groups after counting

-- COMMON MISTAKE: using HAVING instead of WHERE for non-aggregate filters
-- This works but is slower because all rows are aggregated before filtering
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2, 3);  -- should be in WHERE instead

-- CORRECT: move non-aggregate filter to WHERE for better performance
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE department_id IN (1, 2, 3)   -- filters rows before GROUP BY
GROUP BY department_id;
Best Practices

  • Always add a WHERE clause to UPDATE and DELETE statements unless a full-table operation is truly intended.

  • Use parentheses liberally with AND/OR combinations to make operator precedence explicit.

  • Compare NULLs with IS NULL / IS NOT NULL — never with = or !=.

  • Avoid wrapping indexed columns in functions inside WHERE — use equivalent range expressions instead.

  • Prefer EXISTS over IN when the subquery may be large — EXISTS short-circuits on the first match.

  • Use NOT EXISTS instead of NOT IN when the subquery may return NULL values.

  • Write SARGable predicates: keep indexed columns bare on the left side of comparisons.

  • Use EXPLAIN to verify index usage on any WHERE clause that runs frequently.

  • Index columns used in WHERE clauses of hot queries, especially those combined with ORDER BY or LIMIT.

  • Move non-aggregate filters into WHERE (not HAVING) so MySQL can reduce the row set before grouping.