SQL Comparison Operators – =, <>, >, <, >=, <=

The = (Equals) Operator

The = operator returns rows where the column value exactly matches the given value. Works with numbers, strings, and dates.

SQL

-- Numeric equality
SELECT * FROM employees WHERE department_id = 5;

-- String equality (case-insensitive in MySQL by default)
SELECT * FROM users WHERE country = 'US';

-- Date equality
SELECT * FROM orders WHERE order_date = '2026-06-08';

-- Combined with AND
SELECT * FROM products
WHERE category = 'Electronics' AND is_active = 1;

ℹ️

String comparisons in SQL

In MySQL with the default utf8mb4_general_ci collation, WHERE name = 'alice' also matches 'Alice' and 'ALICE' — string comparisons are case-insensitive. In PostgreSQL, string comparisons are case-sensitive by default: 'alice' <> 'Alice'. Be aware of your database's collation settings when filtering on string columns.

<> and != (Not Equal) Operators

Both <> and != mean "not equal." <> is the SQL standard; != is widely supported but technically non-standard. They are interchangeable in MySQL, PostgreSQL, and SQLite.

SQL

-- Exclude a specific department
SELECT * FROM employees WHERE department_id <> 3;

-- Using != (same result)
SELECT * FROM employees WHERE department_id != 3;

-- Exclude a status
SELECT * FROM orders WHERE status <> 'cancelled';

-- Strings: exclude a specific country
SELECT * FROM users WHERE country <> 'US';

>, <, >=, <= (Comparison Range Operators)

These operators compare values on a number line (for numerics) or alphabetically (for strings).

SQL

-- Numeric comparisons
SELECT * FROM products  WHERE price > 100;          -- more than 100
SELECT * FROM products  WHERE price < 50;           -- less than 50
SELECT * FROM employees WHERE salary >= 60000;      -- 60000 or more
SELECT * FROM employees WHERE age <= 30;            -- 30 or younger

-- Date comparisons work the same way (later date = greater value)
SELECT * FROM orders WHERE created_at >= '2026-01-01';
SELECT * FROM events  WHERE event_date < CURRENT_DATE;

-- String comparisons use lexicographic (alphabetical) order
-- 'B' > 'A', 'Z' > 'M', 'b' > 'B' (in case-sensitive collations)
SELECT * FROM products WHERE name > 'M';  -- names starting with N-Z

-- Combining range operators
SELECT * FROM employees
WHERE salary >= 50000 AND salary <= 100000;
-- Tip: this is equivalent to: salary BETWEEN 50000 AND 100000

NULL Comparisons – IS NULL / IS NOT NULL

This is one of the most important rules in SQL: you cannot use = to compare NULL values. NULL represents "unknown" — comparing it to anything (including another NULL) always yields UNKNOWN, which counts as false in a WHERE filter.

SQL

-- WRONG: these never return rows, even if manager_id is NULL
SELECT * FROM employees WHERE manager_id = NULL;   -- always empty!
SELECT * FROM employees WHERE manager_id != NULL;  -- also always empty!

-- CORRECT: use IS NULL and IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;      -- top-level managers
SELECT * FROM employees WHERE manager_id IS NOT NULL;  -- everyone else

-- NULL in expressions also propagates as UNKNOWN
SELECT NULL = NULL;   -- returns NULL (not TRUE)
SELECT NULL + 5;      -- returns NULL

⚠️

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN

In most programming languages, a comparison returns either true or false. In SQL, any comparison involving NULL returns a third value: UNKNOWN. The WHERE clause only passes rows where the condition is TRUE — rows that evaluate to UNKNOWN are filtered out, just like FALSE. This is why WHERE col = NULL silently returns no rows even when NULLs are present. Always use IS NULL or IS NOT NULL.

Complete Operator Reference Table

Operator	Meaning	Example	Returns rows when…
`=`	Equal to	`salary = 50000`	salary is exactly 50000
`<>`	Not equal to (standard)	`status <> 'cancelled'`	status is not 'cancelled'
`!=`	Not equal to (alternate)	`status != 'cancelled'`	same as <>
`>`	Greater than	`price > 100`	price is more than 100
`<`	Less than	`age < 18`	age is less than 18
`>=`	Greater than or equal	`score >= 90`	score is 90 or above
`<=`	Less than or equal	`qty <= 10`	qty is 10 or less
`IS NULL`	Is NULL (unknown)	`manager_id IS NULL`	manager_id is NULL
`IS NOT NULL`	Is not NULL	`email IS NOT NULL`	email has a value
`<=>`	NULL-safe equal (MySQL only)	`manager_id <=> NULL`	manager_id is NULL (returns TRUE, not UNKNOWN)

Summary

📋 Summary

SQL has six standard comparison operators: =, <> (or !=), >, <, >=, <=.
String comparisons are case-insensitive in MySQL (default collation) and case-sensitive in PostgreSQL by default.
Never use = NULL — it always returns UNKNOWN and filters out all rows. Use IS NULL / IS NOT NULL instead.
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. WHERE passes only TRUE rows.
MySQL's <=> is a NULL-safe equality operator that returns TRUE when both sides are NULL.

FAQ

Why does WHERE col = NULL return no rows even if col has NULL values? +

Because NULL represents "unknown" in SQL. Any comparison with NULL — including NULL = NULL — evaluates to UNKNOWN, not TRUE. The WHERE clause only keeps rows where the condition evaluates to TRUE. Rows with UNKNOWN are treated like FALSE and filtered out. This is why you must use the special IS NULL predicate, which tests specifically for the absence of a value rather than comparing values.

What is the difference between <> and !=? +

They are functionally identical in every major SQL database — both mean "not equal to." The difference is only in their origin: <> is the SQL standard (ISO/ANSI), while != is borrowed from programming languages like C and Python. MySQL, PostgreSQL, SQLite, and SQL Server all support both. Best practice is to use <> for SQL standard compliance, especially in code that may need to run on multiple databases.

How do string comparisons work with > and <? +

Strings are compared lexicographically (dictionary order) based on the column's collation. 'apple' < 'banana' because 'a' comes before 'b'. 'Zoo' < 'apple' in case-sensitive collations because uppercase letters have lower code points than lowercase. In MySQL's default case-insensitive collation, 'Zoo' < 'apple' would be false. Practical use: WHERE last_name > 'M' returns last names starting with N through Z. Always be aware of your collation when using >/< on strings.

When would I use MySQL's <=> NULL-safe operator? +

The <=> operator is useful when you want to compare two values that might both be NULL and treat NULL = NULL as TRUE. For example, in an UPDATE or a JOIN condition where you want NULL values on both sides to match: WHERE old_value <=> new_value would match rows where both are NULL (meaning no change) without special IS NULL handling. It is a MySQL-only extension not available in standard SQL or PostgreSQL. In most practical queries, IS NULL and IS NOT NULL are sufficient.