Comparison and Logical Operators in SQL

Why Operators Matter

Operators turn stored values into decisions. A WHERE condition, JOIN condition, CHECK constraint and CASE expression all depend on correct comparisons and logical grouping. A query can be syntactically valid yet return the wrong students when its logic is grouped incorrectly.

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

Comparison Operators

Compare compatible types. Quoted numeric strings can trigger implicit conversion and hide dirty data. Store marks as DECIMAL, dates as DATE/TIMESTAMP and compare them with matching values.

AND, OR and NOT

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

NOT reverses a condition, but a positive form is often clearer: status = 'Inactive' is easier to review than NOT status = 'Active'.

Operator Precedence and Parentheses

MySQL evaluates comparison operators before NOT, NOT before AND, and AND before OR. Therefore these statements are not equivalent:

-- X-A students, plus active X-B students
WHERE class_name = 'X-A'
   OR class_name = 'X-B' AND status = 'Active'

-- Only active students from either class
WHERE (class_name = 'X-A' OR class_name = 'X-B')
  AND status = 'Active'

Three-Valued Logic with NULL

SQL conditions can be TRUE, FALSE or UNKNOWN. Any ordinary comparison with NULL generally becomes UNKNOWN, and WHERE keeps only TRUE.

-- Wrong: returns no NULL marks
SELECT * FROM students WHERE marks = NULL;

-- Correct
SELECT * FROM students WHERE marks IS NULL;

-- MySQL-specific NULL-safe comparison
SELECT * FROM students WHERE marks <=> NULL;

Also remember that NOT (marks = 80) does not include NULL marks, because NOT UNKNOWN remains UNKNOWN. Decide explicitly whether missing data belongs in the result.

Common Mistakes and Practice

• Using = NULL instead of IS NULL.
• Mixing AND/OR without parentheses.
• Comparing numbers as formatted text.
• Using a non-unique name to target UPDATE/DELETE.
• Assuming collation-sensitive text comparison without checking the column collation.

1. Return active X-B students with marks at least 80.
2. Write the same rule with NOT and explain which version is clearer.
3. Predict the difference between the two precedence examples above.
4. Create a truth table row where A is UNKNOWN.

Quick Summary

• Comparison operators test values; logical operators combine tests.
• AND has higher precedence than OR, but explicit parentheses are safest.
• NULL introduces UNKNOWN and requires IS NULL/IS NOT NULL.
• Use compatible data types and precise key conditions.

Official References

• Comparison Functions and Operators — MySQL 8.4
• Logical Operators — MySQL 8.4
• Operator Precedence — MySQL 8.4

References reviewed 14 August 2026. Examples use the common tutorial dataset.