WHERE Clause and Operators

How WHERE Filters Rows

WHERE evaluates a condition for candidate rows and keeps only rows for which the result is TRUE. FALSE and UNKNOWN do not pass the filter. UNKNOWN matters because comparisons involving NULL commonly produce it.

SELECT student_id, full_name, marks
FROM students
WHERE marks >= 88
ORDER BY marks DESC;

The condition is applied to every candidate row semantically. MySQL may use an index to avoid reading all rows, but the returned result must follow the same logic.

Comparison Operators

Use normal = for known values and IS NULL for readable NULL checks. MySQL's <=> is useful in advanced comparisons where two NULL values should count as equal, but it is product-specific.

SELECT full_name
FROM students
WHERE class_name <> 'X-A'
ORDER BY student_id;

Combine Conditions with AND, OR and NOT

SELECT full_name, marks
FROM students
WHERE status = 'Active' AND marks >= 88
ORDER BY marks DESC;

AND requires both conditions. OR requires at least one. NOT reverses a condition. Because AND has higher precedence than OR, always use parentheses when business logic mixes them:

SELECT full_name, class_name, status
FROM students
WHERE (class_name = 'X-A' OR class_name = 'X-B')
  AND status = 'Active'
ORDER BY student_id;

BETWEEN, IN and LIKE

Inclusive range: BETWEEN

SELECT full_name, marks
FROM students
WHERE marks BETWEEN 80 AND 90
ORDER BY marks;

BETWEEN includes both boundaries. If lower and upper values are reversed, no ordinary numeric value matches.

Set membership: IN

SELECT full_name, class_name
FROM students
WHERE class_name IN ('X-A', 'XI-A')
ORDER BY student_id;

IN is clearer than repeating the same column with several OR conditions.

Text pattern: LIKE

SELECT full_name
FROM students
WHERE full_name LIKE 'M%';

In LIKE patterns, % matches zero or more characters and _ matches exactly one character. Case and accent behavior depends on collation. A leading wildcard such as '%era' often prevents efficient use of a normal B-tree index.

NULL and Reliable Date Filters

NULL means no known value. Since marks = NULL is not TRUE, use:

SELECT student_id, full_name
FROM students
WHERE marks IS NULL;

SELECT student_id, full_name
FROM students
WHERE marks IS NOT NULL;

For timestamps, a half-open interval is usually safer than applying a function to the column or guessing the final fractional second:

SELECT student_id, full_name, created_at
FROM students
WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-09-01'
ORDER BY created_at;

This includes all of August and excludes 1 September. It also leaves the indexed column unwrapped, which can make index use easier. Interpret timestamps according to the application's time-zone policy.

Safety, Parameters and Performance

• Preview before changing data: run a SELECT with the exact WHERE condition before UPDATE or DELETE.
• Bind untrusted values: use prepared statements instead of concatenating user input.
• Match data types: compare numeric columns with numbers and date columns with valid date values.
• Avoid unnecessary functions on indexed columns: rewrite a date filter as a range when semantics allow.
• Index measured access paths: high-selectivity, frequently filtered columns may benefit, but every index costs storage and write work.
• Verify with EXPLAIN: do not guess whether an index is used.

SELECT student_id, full_name
FROM students
WHERE class_name = ? AND status = ?;

The question marks are placeholders for a prepared statement. The driver sends bound values separately from SQL syntax.

Practice

1. Return active students whose marks are between 85 and 95 inclusive.
2. Find students in X-A or XI-A using IN.
3. Find names whose second character is a using an underscore pattern.
4. Write a filter for rows with unknown marks.
5. Explain the difference between NOT status = 'Active' and status IS NULL.
6. Write an August 2026 half-open timestamp range and explain both boundaries.

Quick Summary

• WHERE keeps only rows whose condition is TRUE.
• Use comparison operators for known values and IS NULL for missing values.
• AND binds more tightly than OR; parentheses make mixed logic safe.
• BETWEEN is inclusive, IN tests membership and LIKE matches patterns.
• Prepared parameters, preview queries and measured indexing improve safety.

Official References

• SELECT Statement — MySQL 8.4
• Comparison Functions and Operators
• Logical Operators
• Pattern Matching
• Working with NULL Values

References reviewed on 14 August 2026. Results assume the four-row learning dataset.