Comparison Operators

Overview

Comparison operators form the backbone of row filtering in SQL, allowing you to select records that match specific mathematical conditions.

Learning Objectives

• Identify all standard comparison operators: =, != (or <>), <, >, >=, <=.
• Understand how databases compare numbers, strings, and dates.
• Write queries that retrieve subset rows matching comparison bounds.

Detailed Concept Explanation

Comparison operators evaluate expressions and return a boolean result (TRUE, FALSE, or UNKNOWN/NULL).

• =: Checks for equality.
• != or <>: Checks for inequality (both syntaxes are standard, though <> is more universally supported).
• < / >: Less than / Greater than.
• <= / >=: Less than or equal to / Greater than or equal to.

When comparing strings, alphabetical ordering determines comparison outcomes (e.g., 'B' > 'A' is TRUE). When comparing dates, chronologically later dates are "greater than" earlier dates.

Code Examples

SQL

-- Filter products priced strictly above $100
SELECT product_name, price 
FROM products 
WHERE price > 100.0;

-- Filter employees hired on or after a specific date
SELECT name, hire_date 
FROM employees 
WHERE hire_date >= '2026-01-01';

-- Filter users not in a specific status (using inequality)
SELECT username, status 
FROM users 
WHERE status <> 'Suspended';

Best Practices

• Explicit Datatypes: Ensure compared values match column data types to avoid performance-killing implicit conversions.
• Null Safety: Avoid using equality operators (= or !=) to check for NULL values.

Interview Perspective

Interactive Challenges

Summary

Comparison operators examine two column or literal values and return rows matching standard boundary conditions.