Topic 9 of 52
Comparison Operators
Overview
To filter data effectively, you must compare values. SQL provides a robust set of mathematical operators (=, >, <, >=, <=) and the specialized <> or != for 'Not Equal'. These operators work natively on numbers, dates (is a date 'greater than' another date?), and even strings (alphabetical sorting).
Syntax
sql
-- 1. Greater Than / Less Than
SELECT name, salary FROM employees
WHERE salary > 75000;
-- 2. Not Equal (ANSI Standard is <>, but != is widely supported)
SELECT * FROM users
WHERE role <> 'admin';
-- 3. BETWEEN (Inclusive Range Check)
-- Highly optimized compared to checking >= and <= manually!
SELECT * FROM sales
WHERE amount BETWEEN 100 AND 500;
-- 4. IN (Checking against a list of exact values)
SELECT name, department FROM employees
WHERE department IN ('HR', 'Engineering', 'Sales');Common Pitfalls
- Assuming
BETWEEN 10 AND 20excludes the numbers 10 and 20.BETWEENis strictly inclusive in SQL. If you want exclusive math, you must writeWHERE val > 10 AND val < 20. - Using
WHERE department = 'HR' OR department = 'Sales' OR department = 'IT'. This is incredibly verbose and harder for the optimizer to read. Always useWHERE department IN ('HR', 'Sales', 'IT').
Interview Questions
Q:
Does
WHERE name > 'M' work? If so, what does it return?A:
Yes. SQL can mathematically compare strings based on lexicographical (alphabetical) order. It will return all names starting with N through Z.
Real-World Example
Filtering a dashboard to show only the last 7 days of sales data.
example
sql
-- Dates can be mathematically compared!
SELECT order_id, total
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date <= '2026-01-07';
-- Cleaner version using BETWEEN:
SELECT order_id, total
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-07';Check Your Knowledge
Test your understanding of Comparison Operators with these quick questions.