MySQL WHERE Clause Conditions Operators

Comparison and Boolean Conditions

MySQL WHERE filters rows before they are returned, updated, or deleted. Correct filtering depends on three-valued NULL logic, operator precedence, data types, collations, and indexes; preview the exact predicate before a write.

Use =, <>, <, <=, >, and >= for scalar comparisons. Combine predicates with AND, OR, and NOT, and add parentheses whenever mixed operators could be misread. AND binds more tightly than OR, so status = active OR status = trial AND country = IN does not mean both statuses are limited to one country.

Compare compatible types and pass values as bound parameters. Implicit conversion can produce surprising matches and prevent effective index use.

NULL, IN, BETWEEN, and LIKE

NULL represents unknown or absent, so column = NULL is never true. Use IS NULL or IS NOT NULL. Because NOT IN with a NULL in its list can evaluate to unknown for every row, filter nulls from the subquery or use a correlated NOT EXISTS when that expresses the intent.

IN matches a finite set. BETWEEN includes both endpoints; for timestamp ranges, a half-open interval such as created_at >= start AND created_at < next_day avoids fractional-second and end-of-day errors. LIKE uses % for any sequence and _ for one character, with case behavior controlled by collation.

Filtering Writes Safely

Run the predicate as a SELECT first, inspect the count and representative primary keys, then execute UPDATE or DELETE in a transaction when rollback is practical. Keep safe-update protections enabled in interactive tools and include a key condition for targeted changes.

Authorization scope belongs in the predicate too. An application update usually needs both the object ID and tenant or owner condition; checking ownership in a separate earlier query creates race and omission risks.

Sargability and Index Use

A predicate is sargable when MySQL can use an index range or lookup directly. Applying a function to an indexed column, leading-wildcard LIKE, mismatched collations, or implicit type conversion can force scanning. Rewrite DATE(created_at) = ? as a timestamp range when possible.

Use EXPLAIN and measured execution, not guesses. A low-selectivity condition may reasonably scan many rows, and a composite index must align with equality, range, and ordering needs of important queries.

Parameterized Read Predicate

SELECT order_id, customer_id, total, created_at
      FROM orders
      WHERE tenant_id = ?
        AND status IN ('paid', 'shipped')
        AND created_at >= ?
        AND created_at < ?
      ORDER BY created_at DESC
      LIMIT ?;

Preview Before Update

START TRANSACTION;

      SELECT user_id, status
      FROM users
      WHERE tenant_id = 42
        AND last_login_at < '2025-07-01'
        AND status = 'inactive'
      FOR UPDATE;

      UPDATE users
      SET archived_at = CURRENT_TIMESTAMP
      WHERE tenant_id = 42
        AND last_login_at < '2025-07-01'
        AND status = 'inactive';

      -- Verify ROW_COUNT() and affected keys, then COMMIT or ROLLBACK.

Before you move on