SQL 16 🛢️ Comparison Operators

Every WHERE clause is built on comparison operators. The = operator tests equality. The > operator tests ordering. The <> operator tests inequality. Without comparison operators, the WHERE clause has nothing to evaluate. The previous chapter covered the WHERE clause and introduced the comparison operators briefly. This chapter goes deeper into each operator, the rules that govern them, and the subtleties that cause bugs in real queries.

The SQL standard defines a set of comparison operators that work on numbers, strings, dates, and booleans. Some operators are simple: = returns TRUE if the two values are equal. Some are subtle: <> returns NULL if either value is NULL, not TRUE. Understanding the differences is the difference between a query that works and one that silently returns wrong results.

Key point: Every comparison in SQL returns one of three values: TRUE, FALSE, or NULL. This is three-valued logic, not the two-valued logic of most programming languages. A comparison with NULL returns NULL, not FALSE. The WHERE clause includes only the rows where the condition is TRUE. Rows where the condition is FALSE or NULL are excluded. This is why WHERE city = NULL returns no rows, and why WHERE city IS NULL is the only way to find null values.

---

Why comparison operators matter

The WHERE clause is the filter. The comparison operators are the tests. A query that filters by the wrong operator returns the wrong rows. A query that compares with NULL incorrectly returns no rows when it should return many.

The equality problem. The = operator tests whether two values are equal. It works for numbers, strings, dates, and booleans. It does not work for NULL. The = NULL comparison returns NULL, not TRUE. The IS NULL operator is the correct way to test for null.

The ordering problem. The <, >, <=, and >= operators test the order of two values. They work for numbers, strings, and dates. For strings, the comparison uses the collation of the column. For dates, the comparison uses chronological order.

The inequality problem. The <> and != operators test whether two values are different. They are equivalent in most databases. The result is TRUE if the values are different, FALSE if they are the same, and NULL if either value is NULL.

The NULL problem. NULL is not a value. It is the absence of a value. It is not equal to anything, not even to another NULL. The comparison NULL = NULL returns NULL, not TRUE. The IS NULL operator is the only way to test for null.

The trade-off. Three-valued logic is more complex than two-valued logic. It catches bugs that two-valued logic would miss, but it also introduces subtle behaviors that developers must learn. A query that works in a programming language’s if statement may not work in SQL’s WHERE clause.

---

a. Equality and Inequality

The = operator tests whether two values are equal. It returns TRUE if they are equal, FALSE if they are not, and NULL if either value is NULL.

SELECT * FROM customers WHERE city = 'New York';
SELECT * FROM orders WHERE total = 100.00;
SELECT * FROM users WHERE is_active = TRUE;

The first query selects customers in New York. The second selects orders with a total of exactly 100.00. The third selects users whose is_active column is TRUE.

The <> operator (or !=) tests whether two values are different. It returns TRUE if they are different, FALSE if they are the same, and NULL if either value is NULL.

SELECT * FROM orders WHERE status <> 'cancelled';
SELECT * FROM products WHERE price != 0;

The first query selects orders that are not cancelled. The second selects products whose price is not zero.

The two operators are logically complementary for non-null values. a <> b is the same as NOT (a = b) when neither value is null. When either value is null, both return NULL.

A common mistake is to write WHERE status <> 'cancelled' and expect it to include rows where status is null. It does not. Rows with null status are excluded because NULL <> 'cancelled' returns NULL, not TRUE.

---

b. Ordering Comparisons

The <, >, <=, and >= operators test the order of two values.

SELECT * FROM orders WHERE total > 100;
SELECT * FROM orders WHERE total >= 100;
SELECT * FROM products WHERE stock < 10;
SELECT * FROM products WHERE stock <= 10;

The > operator returns TRUE if the left value is strictly greater than the right. The >= operator returns TRUE if the left value is greater than or equal. The < and <= operators are the mirror images.

For numbers, the comparison is numeric. For strings, the comparison uses the collation of the column. For dates, the comparison is chronological. For booleans, TRUE is greater than FALSE in some databases and not in others.

SELECT * FROM customers WHERE last_name > 'M';
SELECT * FROM orders WHERE order_date >= '2026-01-01';

The first query selects customers whose last name comes after ‘M’ in the collation order. The second selects orders placed on or after January 1, 2026.

The ordering comparisons return NULL if either value is NULL. A row with a null total is excluded from WHERE total > 100 because NULL > 100 is NULL, not TRUE.

---

c. The NULL Comparison and IS NULL

The IS NULL operator tests whether a value is null. It is the only way to test for null. The = operator cannot be used because NULL = NULL returns NULL, not TRUE.

SELECT * FROM customers WHERE city IS NULL;
SELECT * FROM customers WHERE city IS NOT NULL;

The IS NULL operator returns TRUE if the value is null, FALSE if it is not. The IS NOT NULL operator is the negation.

The IS NULL operator can be combined with other conditions.

SELECT * FROM orders WHERE total > 100 AND status IS NOT NULL;

The IS DISTINCT FROM operator is a null-safe comparison. It returns TRUE if the values are different, including when one is null and the other is not. It returns FALSE if the values are the same, including when both are null.

SELECT * FROM customers WHERE city IS DISTINCT FROM 'New York';

The query selects customers whose city is not New York, including customers whose city is null. The <> operator would exclude the null rows. The IS DISTINCT FROM operator includes them.

The IS NOT DISTINCT FROM operator is the null-safe equality. It returns TRUE if the values are equal, including when both are null.

SELECT * FROM customers WHERE city IS NOT DISTINCT FROM NULL;

The query selects customers whose city is null. It is equivalent to WHERE city IS NULL.

Operator	Null-safe?	Returns TRUE when
=	No	Both values equal and not null
<>	No	Values different and neither null
IS DISTINCT FROM	Yes	Values different, including one null
IS NOT DISTINCT FROM	Yes	Values equal, including both null
IS NULL	Yes	Value is null
IS NOT NULL	Yes	Value is not null

---

Complete Example Session

This session demonstrates the comparison operators on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10, 2),
    stock        INT,
    category     VARCHAR(50)
);

INSERT INTO products VALUES
    (1, 'Laptop', 999.99, 10, 'Electronics'),
    (2, 'Mouse', 29.99, 50, 'Electronics'),
    (3, 'Keyboard', 79.99, 0, 'Electronics'),
    (4, 'Desk', 299.99, NULL, 'Furniture'),
    (5, 'Chair', NULL, 20, 'Furniture'),
    (6, 'Monitor', 199.99, 15, NULL);

-- ============================================
-- PART 2: EQUALITY
-- ============================================

SELECT * FROM products WHERE category = 'Electronics';

-- Output: Laptop, Mouse, Keyboard

-- ============================================
-- PART 3: INEQUALITY
-- ============================================

SELECT * FROM products WHERE category <> 'Electronics';

-- Output: Desk, Chair
-- Monitor is excluded because category is NULL.

-- ============================================
-- PART 4: GREATER THAN
-- ============================================

SELECT * FROM products WHERE price > 100;

-- Output: Laptop, Desk
-- Chair is excluded because price is NULL.

-- ============================================
-- PART 5: LESS THAN OR EQUAL
-- ============================================

SELECT * FROM products WHERE stock <= 10;

-- Output: Laptop, Keyboard
-- Desk is excluded because stock is NULL.

-- ============================================
-- PART 6: THE NULL TRAP
-- ============================================

SELECT * FROM products WHERE price = NULL;

-- Output: empty
-- The comparison = NULL returns NULL, not TRUE.

SELECT * FROM products WHERE price IS NULL;

-- Output: Chair
-- The IS NULL operator returns TRUE.

-- ============================================
-- PART 7: IS NOT NULL
-- ============================================

SELECT * FROM products WHERE stock IS NOT NULL;

-- Output: Laptop, Mouse, Keyboard, Chair, Monitor

-- ============================================
-- PART 8: THE NULL-SAFE COMPARISON
-- ============================================

SELECT * FROM products WHERE category IS DISTINCT FROM 'Electronics';

-- Output: Desk, Chair, Monitor
-- Monitor is included because IS DISTINCT FROM handles null.

SELECT * FROM products WHERE category IS NOT DISTINCT FROM NULL;

-- Output: Monitor
-- Equivalent to WHERE category IS NULL.

-- ============================================
-- PART 9: THE THREE-VALUED LOGIC
-- ============================================

SELECT
    product_name,
    price,
    price > 100 AS is_expensive,
    price IS NULL AS is_null
FROM products;

-- Output:
--  product_name |  price  | is_expensive | is_null
-- --------------+---------+--------------+---------
--  Laptop       |  999.99 | t            | f
--  Mouse        |   29.99 | f            | f
--  Keyboard     |   79.99 | f            | f
--  Desk         |  299.99 | t            | f
--  Chair        |  null   | null         | t
--  Monitor      |  199.99 | t            | f

-- Chair's is_expensive is null, not false.
-- The comparison with null returns null.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- = : equality
-- <> : inequality
-- > : greater than
-- >= : greater than or equal
-- < : less than
-- <= : less than or equal
-- IS NULL : null check
-- IS NOT NULL : not null check
-- IS DISTINCT FROM : null-safe inequality
-- IS NOT DISTINCT FROM : null-safe equality

The ten parts cover the table, equality, inequality, greater than, less than or equal, the null trap, IS NOT NULL, the null-safe comparison, three-valued logic, and the summary.

---

Quick Reference

The Comparison Operators

Operator	Purpose
=	Equal to
<> or !=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to

The Null Operators

Operator	Purpose
IS NULL	Value is null
IS NOT NULL	Value is not null
IS DISTINCT FROM	Null-safe inequality
IS NOT DISTINCT FROM	Null-safe equality

The Three-Valued Logic

Expression	Result
1 = 1	TRUE
1 = 2	FALSE
1 = NULL	NULL
NULL = NULL	NULL
1 > NULL	NULL
NULL IS NULL	TRUE

The Behavior in WHERE

Condition	Row Included?
TRUE	Yes
FALSE	No
NULL	No

---

Best Practices

✅ Do This:

-- Use IS NULL for null checks
WHERE city IS NULL                                              -- ✅

-- Use IS DISTINCT FROM for null-safe inequality
WHERE city IS DISTINCT FROM 'New York'                          -- ✅

-- Use IS NOT NULL to require a value
WHERE total IS NOT NULL AND total > 100                         -- ✅

-- Use COALESCE to provide a fallback for null
WHERE COALESCE(price, 0) > 100                                  -- ✅

❌ Don’t Do This:

-- Don't use = NULL
WHERE city = NULL  -- never true                                 -- ❌

-- Don't expect <> to include null rows
WHERE status <> 'cancelled'  -- excludes null rows               -- ❌

-- Don't compare with null in ordering
WHERE total > NULL  -- always null                               -- ❌

-- Don't use NOT IN with a subquery that returns null
WHERE id NOT IN (SELECT id FROM t)  -- null poisons the result    -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
= NULL returns no rows	Null comparison returns null	Use IS NULL
<> excludes null rows	Null comparison returns null	Use IS DISTINCT FROM
NOT IN with nulls	Null poisons the result	Use NOT EXISTS
String comparison unexpected	Collation differences	Check the collation
Date comparison wrong	Format mismatch	Use ISO format

---

Real-World Examples

1. Equality

SELECT * FROM customers WHERE city = 'New York';

2. Inequality

SELECT * FROM orders WHERE status <> 'cancelled';

3. Greater Than

SELECT * FROM orders WHERE total > 100;

4. Greater Than or Equal

SELECT * FROM orders WHERE total >= 100;

5. Less Than

SELECT * FROM products WHERE stock < 10;

6. IS NULL

SELECT * FROM customers WHERE city IS NULL;

7. IS NOT NULL

SELECT * FROM orders WHERE total IS NOT NULL;

8. IS DISTINCT FROM

SELECT * FROM customers WHERE city IS DISTINCT FROM 'New York';

9. IS NOT DISTINCT FROM

SELECT * FROM customers WHERE city IS NOT DISTINCT FROM NULL;

10. COALESCE

SELECT * FROM products WHERE COALESCE(price, 0) > 100;

---

Visual

The Comparison Operators

┌──────────────────────────────────────────────┐
│  COMPARISON OPERATORS                        │
│                                              │
│  =       equal to                            │
│  <>      not equal to                        │
│  >       greater than                        │
│  >=      greater than or equal to            │
│  <       less than                           │
│  <=      less than or equal to               │
│                                              │
│  They return TRUE, FALSE, or NULL.           │
│                                              │
└──────────────────────────────────────────────┘

The Three-Valued Logic

┌──────────────────────────────────────────────┐
│  THREE-VALUED LOGIC                          │
│                                              │
│  1 = 1     → TRUE                            │
│  1 = 2     → FALSE                           │
│  1 = NULL  → NULL                            │
│  NULL = NULL → NULL                          │
│                                              │
│  WHERE includes only TRUE.                   │
│  FALSE and NULL are excluded.                │
│                                              │
└──────────────────────────────────────────────┘

The NULL Comparison

┌──────────────────────────────────────────────┐
│  NULL COMPARISON                             │
│                                              │
│  WHERE city = NULL                           │
│    └─ Returns NULL, not TRUE                 │
│    └─ No rows returned                       │
│                                              │
│  WHERE city IS NULL                          │
│    └─ Returns TRUE for null values           │
│    └─ Rows returned                          │
│                                              │
│  Use IS NULL, not = NULL.                    │
│                                              │
└──────────────────────────────────────────────┘

The Null-Safe Operators

┌──────────────────────────────────────────────┐
│  NULL-SAFE OPERATORS                         │
│                                              │
│  IS DISTINCT FROM:                           │
│    └─ TRUE if values differ                  │
│    └─ Handles null on either side            │
│                                              │
│  IS NOT DISTINCT FROM:                       │
│    └─ TRUE if values are equal               │
│    └─ Handles null on either side            │
│                                              │
│  These are the null-safe versions of <> and =│
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
=	Equal to
<> / !=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
IS NULL	Null check
IS NOT NULL	Not null check
IS DISTINCT FROM	Null-safe inequality
IS NOT DISTINCT FROM	Null-safe equality
Three-valued logic	TRUE, FALSE, NULL
WHERE includes	Only TRUE

Key takeaways:

• Every comparison returns TRUE, FALSE, or NULL. This is three-valued logic. The WHERE clause includes only the rows where the condition is TRUE. Rows where the condition is FALSE or NULL are excluded .
• The = operator tests equality. It returns TRUE if the values are equal and neither is null. It returns NULL if either value is null. The = NULL comparison never returns TRUE .
• The <> operator tests inequality. It returns TRUE if the values are different and neither is null. It returns NULL if either value is null. It does not include rows where the value is null .
• The ordering operators compare order. The >, >=, <, and <= operators work for numbers, strings, and dates. They return NULL if either value is null .
• The IS NULL operator is the only way to test for null. The = NULL comparison returns NULL, not TRUE. The IS NULL operator returns TRUE for null values and FALSE for non-null values .
• The IS DISTINCT FROM operator is the null-safe inequality. It returns TRUE if the values are different, including when one is null. It returns FALSE if the values are the same, including when both are null .
• The IS NOT DISTINCT FROM operator is the null-safe equality. It returns TRUE if the values are equal, including when both are null. It is equivalent to IS NULL when comparing to NULL .

Remember: Comparison operators are the building blocks of the WHERE clause. Every comparison returns three values: TRUE, FALSE, or NULL. The WHERE clause includes only TRUE. The = operator does not work with NULL. Use IS NULL for null checks. Use IS DISTINCT FROM for null-safe inequality. Three-valued logic is the rule. The comparison operators are the tools.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!