Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

2 / 5

Comparison Operators: The Building Blocks of Predicates

From Values to Truth

Comparison operators are the atomic units of SQL filtering. Every WHERE clause, no matter how complex, ultimately decomposes into individual comparisons: Is this value equal to that? Is this number greater than that? Does this date fall before that?

Mastering comparison operators means understanding not just their syntax, but their semantics—how they behave with different data types, how they interact with NULL, and how they perform under various conditions. A misunderstood operator can mean the difference between a query that returns exactly the right data and one that silently omits critical rows.

What You Will Learn

By the end of this page, you will command the full repertoire of SQL comparison operators. You'll understand their type-specific behaviors, NULL handling semantics, performance implications, and the subtle pitfalls that catch even experienced developers.

Equality Operators

The equality operator (=) is the most frequently used comparison in SQL. Despite its apparent simplicity, equality comparison has nuances that vary by data type and database system.

Basic Equality Syntax:

equality_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Column equals literal value
SELECT * FROM users WHERE status = 'active';
SELECT * FROM products WHERE category_id = 5;
SELECT * FROM events WHERE event_date = '2024-06-15';
 
-- Column equals another column
SELECT * FROM orders WHERE shipping_address = billing_address;
SELECT * FROM inventory WHERE current_stock = minimum_stock;
 
-- Column equals expression result
SELECT * FROM products WHERE price = cost * 2;
SELECT * FROM employees WHERE hire_date = CURRENT_DATE;
 
-- Multiple equality conditions
SELECT * FROM orders 
WHERE customer_id = 1001 
  AND status = 'pending' 
  AND warehouse_id = 3;

String Equality and Case Sensitivity:

String equality depends heavily on the column's collation—the rules that determine how strings are compared. Different databases have different defaults:

Default String Comparison Behavior by Database
Database	Default Case Sensitivity	Notes
PostgreSQL	Case-Sensitive	Use `ILIKE` or `LOWER()` for case-insensitive
MySQL (utf8mb4_0900_ai_ci)	Case-Insensitive	Default collation is accent/case insensitive
SQL Server	Depends on collation	Database-level setting, often case-insensitive
Oracle	Case-Sensitive	Use `UPPER()` or `LOWER()` for case-insensitive
SQLite	Case-Sensitive for ASCII	Unicode behavior depends on build options

string_equality_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- PostgreSQL: Case-sensitive by default
SELECT * FROM users WHERE username = 'JohnDoe';     -- Won't match 'johndoe'
 
-- PostgreSQL: Force case-insensitive
SELECT * FROM users WHERE LOWER(username) = LOWER('JohnDoe');
SELECT * FROM users WHERE username ILIKE 'johndoe'; -- PostgreSQL-specific
 
-- MySQL: Case-insensitive by default (depends on collation)
SELECT * FROM users WHERE username = 'JohnDoe';     -- Matches 'johndoe'
 
-- MySQL: Force case-sensitive comparison
SELECT * FROM users WHERE username = BINARY 'JohnDoe';
SELECT * FROM users WHERE username COLLATE utf8mb4_bin = 'JohnDoe';
 
-- SQL Server: Use COLLATE for explicit control
SELECT * FROM users 
WHERE username COLLATE Latin1_General_CS_AS = 'JohnDoe';  -- Case-sensitive
 
-- Oracle: Case-sensitive by default
SELECT * FROM users WHERE UPPER(username) = UPPER('JohnDoe');

The LOWER()/UPPER() Performance Trap

Using LOWER() or UPPER() on a column in WHERE prevents index usage—the database can't use an index on username for a query on LOWER(username). Solutions: create a functional index on LOWER(username), add a computed column, or store normalized data separately.

Inequality Operators

Inequality operators test that values are not equal. SQL provides two syntactic variants that are semantically identical:

<> — The SQL standard inequality operator
!= — Alternative syntax (widely supported but technically not standard)

Basic Inequality Usage:

inequality_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Standard SQL inequality
SELECT * FROM orders WHERE status <> 'cancelled';
SELECT * FROM products WHERE category_id <> 0;
 
-- Alternative syntax (equivalent)
SELECT * FROM orders WHERE status != 'cancelled';
SELECT * FROM products WHERE category_id != 0;
 
-- Multiple inequalities
SELECT * FROM employees 
WHERE department <> 'HR' 
  AND department <> 'Legal';
 
-- Equivalent using NOT IN (more readable for multiple exclusions)
SELECT * FROM employees 
WHERE department NOT IN ('HR', 'Legal');
 
-- Inequality with expressions
SELECT * FROM products WHERE sale_price <> regular_price;
SELECT * FROM orders WHERE shipped_date <> estimated_date;

Inequality and NULL

Because NULL represents an unknown value, column <> value returns UNKNOWN (not TRUE) when the column is NULL. This means rows with NULL in the compared column are excluded from results—even though NULL is clearly 'not equal' to any specific value in an intuitive sense. Always handle NULLs explicitly when using inequality.

inequality_null_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Problem: Missing NULL rows with simple inequality
-- Table: employees (id, manager_id)
-- Some employees have NULL manager_id (they're top-level)
 
-- This query excludes employees with NULL manager_id!
SELECT * FROM employees WHERE manager_id <> 5;
 
-- Solution: Explicitly include NULL
SELECT * FROM employees 
WHERE manager_id <> 5 OR manager_id IS NULL;
 
-- Alternative: Use NULL-safe comparison (MySQL)
SELECT * FROM employees WHERE NOT (manager_id <=> 5);
 
-- Alternative: COALESCE (if you can define a 'default' value)
SELECT * FROM employees WHERE COALESCE(manager_id, -1) <> 5;

Performance Considerations:

Inequality predicates have different index usage characteristics than equality:

Equality (=): Can perform index seek—directly locate matching rows
Inequality (<>): Often requires index scan or table scan—must examine most/all rows to find non-matches

This asymmetry exists because indexes are organized to find matches, not exclusions. A query finding status = 'active' among 1000 rows might need to examine only 50 matching rows. But status <> 'deleted' might need to examine 950 non-matching rows.

Relational Operators

Relational operators compare ordered values: less than, greater than, and their inclusive variants. These operators are the workhorses of range queries—finding values within boundaries, filtering by date ranges, or selecting records above thresholds.

The Four Relational Operators:

SQL Relational Operators
Operator	Meaning	Returns TRUE when
`<`	Less than	Left operand is strictly less than right operand
`<=`	Less than or equal	Left operand is less than or equal to right operand
`>`	Greater than	Left operand is strictly greater than right operand
`>=`	Greater than or equal	Left operand is greater than or equal to right operand

relational_operators.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Numeric comparisons
SELECT * FROM products WHERE price < 100;           -- Under $100
SELECT * FROM products WHERE price <= 100;          -- $100 or less
SELECT * FROM orders WHERE total > 500;             -- Over $500
SELECT * FROM inventory WHERE quantity >= 10;       -- At least 10 units
 
-- Date comparisons
SELECT * FROM orders WHERE order_date < '2024-01-01';   -- Before 2024
SELECT * FROM subscriptions WHERE expiry > CURRENT_DATE; -- Not yet expired
SELECT * FROM employees WHERE hire_date >= '2020-03-15'; -- Hired Mar 15 or later
 
-- Time comparisons
SELECT * FROM sessions 
WHERE last_activity < NOW() - INTERVAL '30 minutes';    -- Inactive sessions
 
-- Combined range (explicit boundaries)
SELECT * FROM products 
WHERE price >= 50 AND price < 100;  -- $50 to $99.99
 
-- Column-to-column relational comparison
SELECT * FROM products WHERE sale_price < regular_price;  -- Items on sale
SELECT * FROM orders WHERE actual_ship_date > promised_date; -- Late shipments

String Ordering:

Relational operators work on strings using lexicographic (dictionary) ordering, which depends on the column's collation. This ordering considers character codes, case rules, and locale-specific conventions:

string_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Alphabetic ranges
SELECT * FROM products WHERE name < 'M';     -- Products A-L
SELECT * FROM products WHERE name >= 'S';    -- Products S-Z
 
-- Numeric strings (lexicographic, not numeric!)
SELECT * FROM codes WHERE code > '100';
-- '2' > '100' is TRUE (because '2' > '1' character-by-character)
-- This is often a bug when storing numbers as strings
 
-- Case sensitivity affects ordering
-- In case-sensitive collations: 'Z' < 'a' (uppercase before lowercase in ASCII)
-- In case-insensitive collations: 'a' < 'Z' (treats as 'A' < 'Z')
 
-- Prefix matching with range operators
SELECT * FROM products WHERE name >= 'App' AND name < 'Aps';
-- Finds: 'App', 'Apple', 'Application', 'Appliance'
-- Excludes: 'Apt', 'Aqua', etc.

Indexable Range Queries

Relational operators are highly indexable. A B-tree index can efficiently satisfy price > 100 by seeking to the first value above 100 and scanning from there. This makes range queries on indexed columns very fast, even on large tables.

NULL Comparison Operators

NULL requires special operators because standard comparisons with NULL yield UNKNOWN. SQL provides dedicated operators for NULL testing:

IS NULL and IS NOT NULL:

These are the only reliable ways to test for NULL in standard SQL:

null_operators.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Test for NULL values
SELECT * FROM users WHERE phone IS NULL;           -- No phone recorded
SELECT * FROM orders WHERE shipped_date IS NULL;   -- Not yet shipped
 
-- Test for non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;       -- Phone recorded
SELECT * FROM employees WHERE termination_date IS NOT NULL; -- Former employees
 
-- Common pattern: Optional fields
SELECT * FROM customers 
WHERE secondary_email IS NULL;  -- Customers without secondary email
 
-- NULL in expressions
SELECT * FROM products 
WHERE (discontinued_date IS NULL) OR (discontinued_date > CURRENT_DATE);

Never Use = NULL or <> NULL

The expressions column = NULL and column <> NULL always evaluate to UNKNOWN, never TRUE. This returns zero rows, regardless of the data. Some databases have settings (like ANSI_NULLS in SQL Server) that change this behavior, but relying on such settings is poor practice.

IS DISTINCT FROM (SQL:2011 Standard):

This operator treats NULL as a comparable value—two NULLs are considered equal, and NULL is considered distinct from any non-NULL value. This matches typical programming language semantics:

is_distinct_from.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- IS DISTINCT FROM treats NULL as a value
-- PostgreSQL syntax
SELECT * FROM employees 
WHERE manager_id IS DISTINCT FROM 5;
-- Returns: employees whose manager is not 5, INCLUDING those with NULL manager_id
 
SELECT * FROM employees 
WHERE manager_id IS NOT DISTINCT FROM NULL;
-- Returns: employees with NULL manager_id (equivalent to IS NULL)
 
-- Comparison with regular operators:
-- manager_id <> 5          → Excludes NULL (UNKNOWN treated as FALSE)
-- manager_id IS DISTINCT FROM 5  → Includes NULL (NULL ≠ 5 is TRUE)
 
-- MySQL equivalent: <=> (NULL-safe equality)
SELECT * FROM employees WHERE NOT (manager_id <=> 5);
 
-- SQL Server 2022+: IS DISTINCT FROM supported
SELECT * FROM employees WHERE manager_id IS DISTINCT FROM 5;
 
-- Older SQL Server workaround:
SELECT * FROM employees 
WHERE (manager_id <> 5 OR manager_id IS NULL);

NULL Comparison Behavior
Expression	When a = NULL	When a = 5	When a = 10
`a = 5`	UNKNOWN	TRUE	FALSE
`a <> 5`	UNKNOWN	FALSE	TRUE
`a IS NULL`	TRUE	FALSE	FALSE
`a IS NOT NULL`	FALSE	TRUE	TRUE
`a IS DISTINCT FROM 5`	TRUE	FALSE	TRUE
`a IS NOT DISTINCT FROM 5`	FALSE	TRUE	FALSE

Multi-Value Comparison

SQL provides operators for comparing a single value against multiple values simultaneously. These are syntactic conveniences that expand to simpler predicates.

ANY, SOME, and ALL:

These operators compare a value against a set (usually from a subquery):

any_some_all.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- ANY (or SOME): TRUE if comparison holds for at least one set member
SELECT * FROM products 
WHERE price > ANY (SELECT price FROM products WHERE category = 'Premium');
-- Products priced higher than the cheapest premium product
 
SELECT * FROM employees
WHERE salary >= SOME (SELECT salary FROM employees WHERE department = 'Executive');
-- Employees earning at least as much as some executive
 
-- ALL: TRUE if comparison holds for every set member
SELECT * FROM products 
WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');
-- Products priced higher than every budget product
 
SELECT * FROM students
WHERE grade >= ALL (SELECT min_grade FROM course_requirements WHERE course_id = 101);
-- Students meeting all grade requirements for course 101
 
-- Equivalences:
-- value > ANY (set)  ≡  value > MIN(set)
-- value < ANY (set)  ≡  value < MAX(set)
-- value > ALL (set)  ≡  value > MAX(set)
-- value < ALL (set)  ≡  value < MIN(set)
-- value = ANY (set)  ≡  value IN (set)

Row Value Comparison:

SQL allows comparing tuples (multiple columns) as a unit. This is powerful for composite ordering and pagination:

row_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Row value equality
SELECT * FROM order_items 
WHERE (order_id, product_id) = (1001, 'PROD-X');
 
-- Row value in list
SELECT * FROM order_items
WHERE (order_id, line_number) IN ((1001, 1), (1001, 2), (1002, 1));
 
-- Row value range comparison (lexicographic)
-- Useful for keyset pagination (faster than OFFSET)
SELECT * FROM events
WHERE (event_date, event_id) > ('2024-06-15', 1000)
ORDER BY event_date, event_id
LIMIT 20;
-- Gets the next 20 events after the specified date/id combination
 
-- Comparing rows from different tables
SELECT * FROM current_prices c
JOIN historical_prices h ON c.product_id = h.product_id
WHERE (c.price, c.discount) <> (h.price, h.discount);
-- Find products where price or discount changed

Keyset Pagination

Row value comparison enables keyset pagination (also called cursor-based pagination). Instead of OFFSET 10000, LIMIT 20 which must skip 10000 rows, you use WHERE (date, id) > (last_seen_date, last_seen_id) which can seek directly to the right position using an index. Essential for large result sets.

Type-Specific Comparison Behavior

Comparison operators behave differently depending on the data types involved. Understanding these nuances prevents subtle bugs.

Numeric Type Comparisons:

numeric_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Integer comparisons are exact
SELECT * FROM products WHERE quantity = 100;        -- Exactly 100
 
-- DECIMAL/NUMERIC comparisons are exact
SELECT * FROM invoices WHERE amount = 1234.56;      -- Exactly 1234.56
 
-- FLOAT/DOUBLE comparisons can be imprecise
SELECT * FROM measurements WHERE value = 0.1 + 0.2;
-- May not match 0.3 due to floating-point representation!
 
-- Safe floating-point comparison patterns:
SELECT * FROM measurements WHERE ABS(value - 0.3) < 0.0001;  -- Epsilon comparison
SELECT * FROM measurements WHERE value BETWEEN 0.2999 AND 0.3001;
 
-- Comparing different numeric types (implicit coercion)
SELECT * FROM products WHERE id = '100';            -- String coerced to integer
-- WARNING: This may cause index to not be used, or unexpected matches
 
-- Avoid implicit coercion
SELECT * FROM products WHERE id = CAST('100' AS INTEGER);

Date and Time Comparisons:

datetime_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- DATE comparisons (day precision)
SELECT * FROM orders WHERE order_date = '2024-06-15';
 
-- TIMESTAMP comparisons (includes time component)
SELECT * FROM events WHERE event_time = '2024-06-15 14:30:00';
 
-- Beware: DATE vs TIMESTAMP mismatch
-- If order_date is TIMESTAMP, this may miss orders:
SELECT * FROM orders WHERE order_date = '2024-06-15';
-- The literal '2024-06-15' becomes '2024-06-15 00:00:00'
-- So 2024-06-15 14:30:00 won't match!
 
-- Safe pattern for selecting a specific day from TIMESTAMP columns:
SELECT * FROM orders 
WHERE order_date >= '2024-06-15' 
  AND order_date < '2024-06-16';
 
-- Or truncate the timestamp:
SELECT * FROM orders WHERE DATE(order_date) = '2024-06-15';  -- MySQL
SELECT * FROM orders WHERE order_date::DATE = '2024-06-15'; -- PostgreSQL
-- WARNING: Truncation prevents index usage
 
-- Time zone considerations
SELECT * FROM events 
WHERE event_time AT TIME ZONE 'America/New_York' >= '2024-06-15 09:00:00';

The Timestamp Truncation Pattern

Using DATE(timestamp_column) or TRUNC(timestamp_column) in WHERE clauses prevents index usage. For high-performance date filtering on timestamp columns, always use range conditions: >= start_of_day AND < start_of_next_day.

Boolean Comparisons:

boolean_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Explicit comparison
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active = FALSE;
 
-- Implicit comparison (PostgreSQL, MySQL)
SELECT * FROM users WHERE is_active;          -- Equivalent to = TRUE
SELECT * FROM users WHERE NOT is_active;      -- Equivalent to = FALSE
 
-- IS TRUE, IS FALSE, IS UNKNOWN (SQL standard)
SELECT * FROM users WHERE is_verified IS TRUE;    -- Only TRUE (excludes NULL)
SELECT * FROM users WHERE is_verified IS FALSE;   -- Only FALSE (excludes NULL)
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
 
-- Three-state boolean handling
-- If is_verified can be TRUE, FALSE, or NULL (unknown):
SELECT COUNT(*) AS verified FROM users WHERE is_verified IS TRUE;
SELECT COUNT(*) AS unverified FROM users WHERE is_verified IS FALSE;
SELECT COUNT(*) AS unknown FROM users WHERE is_verified IS NULL;

Comparison Operator Precedence

When combining comparison operators with logical operators (AND, OR, NOT), precedence determines evaluation order. Misunderstanding precedence is a common source of query bugs.

Standard SQL Operator Precedence (highest to lowest):

SQL Operator Precedence
Precedence	Operators	Description
1 (highest)	Parentheses `()`	Explicit grouping
2	Unary `+`, `-`, `~`	Sign, bitwise NOT
3	`*`, `/`, `%`	Multiplication, division, modulo
4	`+`, `-`	Addition, subtraction
5	Comparison `=`, `<>`, `<`, `>`, `<=`, `>=`	All comparison operators
6	`NOT`	Logical negation
7	`AND`	Logical conjunction
8 (lowest)	`OR`	Logical disjunction

precedence_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Precedence trap: AND binds tighter than OR
-- WRONG interpretation: Find active users who are admins or moderators
SELECT * FROM users WHERE role = 'admin' OR role = 'moderator' AND is_active = TRUE;
-- This is parsed as: role = 'admin' OR (role = 'moderator' AND is_active = TRUE)
-- Returns: ALL admins (even inactive) plus active moderators
 
-- CORRECT: Use parentheses to clarify intent
SELECT * FROM users WHERE (role = 'admin' OR role = 'moderator') AND is_active = TRUE;
-- Returns: Active admins and active moderators
 
-- Another common mistake
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' AND priority > 5;
-- Parsed as: status = 'pending' OR (status = 'processing' AND priority > 5)
-- Gets ALL pending orders plus high-priority processing orders
 
-- What you probably meant:
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing') AND priority > 5;
-- Gets high-priority orders that are pending or processing
 
-- When in doubt, add parentheses
-- Explicit is better than relying on precedence memory

The Parentheses Rule

When combining AND and OR in the same WHERE clause, always use parentheses to make your intention explicit. Future readers (including yourself) will thank you. The seconds spent typing parentheses save hours debugging incorrect query results.

Performance Implications of Comparison Choices

Different comparison patterns have dramatically different performance characteristics. Understanding these enables you to write queries that scale.

Index-Friendly Comparisons:

Indexable Patterns

•column = value (equality)
•column > value (range)
•column >= value AND column < value (bounded range)
•column IN (v1, v2, v3) (multiple equality)
•column IS NULL
•column LIKE 'prefix%' (trailing wildcard)

Non-Indexable Patterns

•LOWER(column) = value (function on column)
•column + 1 = value (arithmetic on column)
•column <> value (inequality often scans all)
•column LIKE '%suffix' (leading wildcard)
•column1 = column2 (variable both sides)
•CAST(column AS type) = value (type conversion)

performance_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Verify index usage with EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
-- Look for: Index Seek, Index Scan vs. Table Scan
 
-- Transform non-sargable to sargable
-- Instead of:
SELECT * FROM events WHERE YEAR(event_date) = 2024;
-- Use:
SELECT * FROM events 
WHERE event_date >= '2024-01-01' AND event_date < '2025-01-01';
 
-- Instead of:
SELECT * FROM products WHERE price * 1.1 < 100;
-- Use:
SELECT * FROM products WHERE price < 100 / 1.1;
 
-- Instead of:
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
-- Use (if functional index exists):
SELECT * FROM users WHERE email_lower = 'john@example.com';  -- computed column
-- Or create a functional index on LOWER(email)
 
-- Composite index considerations
-- Index on (status, created_at) works for:
SELECT * FROM orders WHERE status = 'pending';                      -- Yes
SELECT * FROM orders WHERE status = 'pending' AND created_at > x;   -- Yes
SELECT * FROM orders WHERE created_at > x;                          -- Limited benefit
-- The leftmost columns of a composite index must be in the query

The Optimizer's Judgment

Even with index-friendly predicates, the optimizer may choose a table scan if the selectivity is low (many rows match). Selecting 80% of a table is often faster with a table scan than an index scan because sequential I/O is faster than random I/O. Trust the optimizer, but verify with EXPLAIN.

Summary: Mastering Comparison Operators

Comparison operators are the foundation of SQL filtering. Their correct use determines not just whether your queries return correct results, but whether they perform acceptably at scale.

Key Takeaways

•Equality comparisons are type and collation sensitive — Case sensitivity and encoding affect results
•Inequality excludes NULL rows — Always explicitly handle NULL when using <>
•Relational operators enable range queries — These are highly indexable and efficient
•Use IS NULL and IS DISTINCT FROM for NULL handling — Standard comparison operators fail with NULL
•Operator precedence matters — AND binds tighter than OR; use parentheses for clarity
•Data type affects comparison behavior — Floating-point, datetime, and boolean types have nuances
•Sargability determines performance — Keep indexed columns 'naked' in comparisons

What's Next:

With comparison operators mastered, we move to the BETWEEN operator—SQL's elegant syntax for range queries. You'll learn when BETWEEN simplifies your code, its boundary behavior, and the subtle ways it differs from explicit range comparisons.

Page Complete

You now have a deep understanding of SQL comparison operators—from basic equality to NULL-safe comparisons to performance implications. These operators are the atoms from which all WHERE clause molecules are built.

2 / 5

Loading learning content...

Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

2 / 5

Comparison Operators: The Building Blocks of Predicates

From Values to Truth

What You Will Learn

Equality Operators

The equality operator (=) is the most frequently used comparison in SQL. Despite its apparent simplicity, equality comparison has nuances that vary by data type and database system.

Basic Equality Syntax:

equality_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Column equals literal value
SELECT * FROM users WHERE status = 'active';
SELECT * FROM products WHERE category_id = 5;
SELECT * FROM events WHERE event_date = '2024-06-15';
 
-- Column equals another column
SELECT * FROM orders WHERE shipping_address = billing_address;
SELECT * FROM inventory WHERE current_stock = minimum_stock;
 
-- Column equals expression result
SELECT * FROM products WHERE price = cost * 2;
SELECT * FROM employees WHERE hire_date = CURRENT_DATE;
 
-- Multiple equality conditions
SELECT * FROM orders 
WHERE customer_id = 1001 
  AND status = 'pending' 
  AND warehouse_id = 3;

String Equality and Case Sensitivity:

String equality depends heavily on the column's collation—the rules that determine how strings are compared. Different databases have different defaults:

Default String Comparison Behavior by Database
Database	Default Case Sensitivity	Notes
PostgreSQL	Case-Sensitive	Use `ILIKE` or `LOWER()` for case-insensitive
MySQL (utf8mb4_0900_ai_ci)	Case-Insensitive	Default collation is accent/case insensitive
SQL Server	Depends on collation	Database-level setting, often case-insensitive
Oracle	Case-Sensitive	Use `UPPER()` or `LOWER()` for case-insensitive
SQLite	Case-Sensitive for ASCII	Unicode behavior depends on build options

string_equality_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- PostgreSQL: Case-sensitive by default
SELECT * FROM users WHERE username = 'JohnDoe';     -- Won't match 'johndoe'
 
-- PostgreSQL: Force case-insensitive
SELECT * FROM users WHERE LOWER(username) = LOWER('JohnDoe');
SELECT * FROM users WHERE username ILIKE 'johndoe'; -- PostgreSQL-specific
 
-- MySQL: Case-insensitive by default (depends on collation)
SELECT * FROM users WHERE username = 'JohnDoe';     -- Matches 'johndoe'
 
-- MySQL: Force case-sensitive comparison
SELECT * FROM users WHERE username = BINARY 'JohnDoe';
SELECT * FROM users WHERE username COLLATE utf8mb4_bin = 'JohnDoe';
 
-- SQL Server: Use COLLATE for explicit control
SELECT * FROM users 
WHERE username COLLATE Latin1_General_CS_AS = 'JohnDoe';  -- Case-sensitive
 
-- Oracle: Case-sensitive by default
SELECT * FROM users WHERE UPPER(username) = UPPER('JohnDoe');

The LOWER()/UPPER() Performance Trap

Inequality Operators

Inequality operators test that values are not equal. SQL provides two syntactic variants that are semantically identical:

<> — The SQL standard inequality operator
!= — Alternative syntax (widely supported but technically not standard)

Basic Inequality Usage:

inequality_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Standard SQL inequality
SELECT * FROM orders WHERE status <> 'cancelled';
SELECT * FROM products WHERE category_id <> 0;
 
-- Alternative syntax (equivalent)
SELECT * FROM orders WHERE status != 'cancelled';
SELECT * FROM products WHERE category_id != 0;
 
-- Multiple inequalities
SELECT * FROM employees 
WHERE department <> 'HR' 
  AND department <> 'Legal';
 
-- Equivalent using NOT IN (more readable for multiple exclusions)
SELECT * FROM employees 
WHERE department NOT IN ('HR', 'Legal');
 
-- Inequality with expressions
SELECT * FROM products WHERE sale_price <> regular_price;
SELECT * FROM orders WHERE shipped_date <> estimated_date;

Inequality and NULL

inequality_null_handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Problem: Missing NULL rows with simple inequality
-- Table: employees (id, manager_id)
-- Some employees have NULL manager_id (they're top-level)
 
-- This query excludes employees with NULL manager_id!
SELECT * FROM employees WHERE manager_id <> 5;
 
-- Solution: Explicitly include NULL
SELECT * FROM employees 
WHERE manager_id <> 5 OR manager_id IS NULL;
 
-- Alternative: Use NULL-safe comparison (MySQL)
SELECT * FROM employees WHERE NOT (manager_id <=> 5);
 
-- Alternative: COALESCE (if you can define a 'default' value)
SELECT * FROM employees WHERE COALESCE(manager_id, -1) <> 5;

Performance Considerations:

Inequality predicates have different index usage characteristics than equality:

Equality (=): Can perform index seek—directly locate matching rows
Inequality (<>): Often requires index scan or table scan—must examine most/all rows to find non-matches

Relational Operators

The Four Relational Operators:

SQL Relational Operators
Operator	Meaning	Returns TRUE when
`<`	Less than	Left operand is strictly less than right operand
`<=`	Less than or equal	Left operand is less than or equal to right operand
`>`	Greater than	Left operand is strictly greater than right operand
`>=`	Greater than or equal	Left operand is greater than or equal to right operand

relational_operators.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Numeric comparisons
SELECT * FROM products WHERE price < 100;           -- Under $100
SELECT * FROM products WHERE price <= 100;          -- $100 or less
SELECT * FROM orders WHERE total > 500;             -- Over $500
SELECT * FROM inventory WHERE quantity >= 10;       -- At least 10 units
 
-- Date comparisons
SELECT * FROM orders WHERE order_date < '2024-01-01';   -- Before 2024
SELECT * FROM subscriptions WHERE expiry > CURRENT_DATE; -- Not yet expired
SELECT * FROM employees WHERE hire_date >= '2020-03-15'; -- Hired Mar 15 or later
 
-- Time comparisons
SELECT * FROM sessions 
WHERE last_activity < NOW() - INTERVAL '30 minutes';    -- Inactive sessions
 
-- Combined range (explicit boundaries)
SELECT * FROM products 
WHERE price >= 50 AND price < 100;  -- $50 to $99.99
 
-- Column-to-column relational comparison
SELECT * FROM products WHERE sale_price < regular_price;  -- Items on sale
SELECT * FROM orders WHERE actual_ship_date > promised_date; -- Late shipments

String Ordering:

string_ordering.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Alphabetic ranges
SELECT * FROM products WHERE name < 'M';     -- Products A-L
SELECT * FROM products WHERE name >= 'S';    -- Products S-Z
 
-- Numeric strings (lexicographic, not numeric!)
SELECT * FROM codes WHERE code > '100';
-- '2' > '100' is TRUE (because '2' > '1' character-by-character)
-- This is often a bug when storing numbers as strings
 
-- Case sensitivity affects ordering
-- In case-sensitive collations: 'Z' < 'a' (uppercase before lowercase in ASCII)
-- In case-insensitive collations: 'a' < 'Z' (treats as 'A' < 'Z')
 
-- Prefix matching with range operators
SELECT * FROM products WHERE name >= 'App' AND name < 'Aps';
-- Finds: 'App', 'Apple', 'Application', 'Appliance'
-- Excludes: 'Apt', 'Aqua', etc.

Indexable Range Queries

NULL Comparison Operators

NULL requires special operators because standard comparisons with NULL yield UNKNOWN. SQL provides dedicated operators for NULL testing:

IS NULL and IS NOT NULL:

These are the only reliable ways to test for NULL in standard SQL:

null_operators.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Test for NULL values
SELECT * FROM users WHERE phone IS NULL;           -- No phone recorded
SELECT * FROM orders WHERE shipped_date IS NULL;   -- Not yet shipped
 
-- Test for non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;       -- Phone recorded
SELECT * FROM employees WHERE termination_date IS NOT NULL; -- Former employees
 
-- Common pattern: Optional fields
SELECT * FROM customers 
WHERE secondary_email IS NULL;  -- Customers without secondary email
 
-- NULL in expressions
SELECT * FROM products 
WHERE (discontinued_date IS NULL) OR (discontinued_date > CURRENT_DATE);

Never Use = NULL or <> NULL

IS DISTINCT FROM (SQL:2011 Standard):

This operator treats NULL as a comparable value—two NULLs are considered equal, and NULL is considered distinct from any non-NULL value. This matches typical programming language semantics:

is_distinct_from.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- IS DISTINCT FROM treats NULL as a value
-- PostgreSQL syntax
SELECT * FROM employees 
WHERE manager_id IS DISTINCT FROM 5;
-- Returns: employees whose manager is not 5, INCLUDING those with NULL manager_id
 
SELECT * FROM employees 
WHERE manager_id IS NOT DISTINCT FROM NULL;
-- Returns: employees with NULL manager_id (equivalent to IS NULL)
 
-- Comparison with regular operators:
-- manager_id <> 5          → Excludes NULL (UNKNOWN treated as FALSE)
-- manager_id IS DISTINCT FROM 5  → Includes NULL (NULL ≠ 5 is TRUE)
 
-- MySQL equivalent: <=> (NULL-safe equality)
SELECT * FROM employees WHERE NOT (manager_id <=> 5);
 
-- SQL Server 2022+: IS DISTINCT FROM supported
SELECT * FROM employees WHERE manager_id IS DISTINCT FROM 5;
 
-- Older SQL Server workaround:
SELECT * FROM employees 
WHERE (manager_id <> 5 OR manager_id IS NULL);

NULL Comparison Behavior
Expression	When a = NULL	When a = 5	When a = 10
`a = 5`	UNKNOWN	TRUE	FALSE
`a <> 5`	UNKNOWN	FALSE	TRUE
`a IS NULL`	TRUE	FALSE	FALSE
`a IS NOT NULL`	FALSE	TRUE	TRUE
`a IS DISTINCT FROM 5`	TRUE	FALSE	TRUE
`a IS NOT DISTINCT FROM 5`	FALSE	TRUE	FALSE

Multi-Value Comparison

SQL provides operators for comparing a single value against multiple values simultaneously. These are syntactic conveniences that expand to simpler predicates.

ANY, SOME, and ALL:

These operators compare a value against a set (usually from a subquery):

any_some_all.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- ANY (or SOME): TRUE if comparison holds for at least one set member
SELECT * FROM products 
WHERE price > ANY (SELECT price FROM products WHERE category = 'Premium');
-- Products priced higher than the cheapest premium product
 
SELECT * FROM employees
WHERE salary >= SOME (SELECT salary FROM employees WHERE department = 'Executive');
-- Employees earning at least as much as some executive
 
-- ALL: TRUE if comparison holds for every set member
SELECT * FROM products 
WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');
-- Products priced higher than every budget product
 
SELECT * FROM students
WHERE grade >= ALL (SELECT min_grade FROM course_requirements WHERE course_id = 101);
-- Students meeting all grade requirements for course 101
 
-- Equivalences:
-- value > ANY (set)  ≡  value > MIN(set)
-- value < ANY (set)  ≡  value < MAX(set)
-- value > ALL (set)  ≡  value > MAX(set)
-- value < ALL (set)  ≡  value < MIN(set)
-- value = ANY (set)  ≡  value IN (set)

Row Value Comparison:

SQL allows comparing tuples (multiple columns) as a unit. This is powerful for composite ordering and pagination:

row_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Row value equality
SELECT * FROM order_items 
WHERE (order_id, product_id) = (1001, 'PROD-X');
 
-- Row value in list
SELECT * FROM order_items
WHERE (order_id, line_number) IN ((1001, 1), (1001, 2), (1002, 1));
 
-- Row value range comparison (lexicographic)
-- Useful for keyset pagination (faster than OFFSET)
SELECT * FROM events
WHERE (event_date, event_id) > ('2024-06-15', 1000)
ORDER BY event_date, event_id
LIMIT 20;
-- Gets the next 20 events after the specified date/id combination
 
-- Comparing rows from different tables
SELECT * FROM current_prices c
JOIN historical_prices h ON c.product_id = h.product_id
WHERE (c.price, c.discount) <> (h.price, h.discount);
-- Find products where price or discount changed

Keyset Pagination

Type-Specific Comparison Behavior

Comparison operators behave differently depending on the data types involved. Understanding these nuances prevents subtle bugs.

Numeric Type Comparisons:

numeric_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Integer comparisons are exact
SELECT * FROM products WHERE quantity = 100;        -- Exactly 100
 
-- DECIMAL/NUMERIC comparisons are exact
SELECT * FROM invoices WHERE amount = 1234.56;      -- Exactly 1234.56
 
-- FLOAT/DOUBLE comparisons can be imprecise
SELECT * FROM measurements WHERE value = 0.1 + 0.2;
-- May not match 0.3 due to floating-point representation!
 
-- Safe floating-point comparison patterns:
SELECT * FROM measurements WHERE ABS(value - 0.3) < 0.0001;  -- Epsilon comparison
SELECT * FROM measurements WHERE value BETWEEN 0.2999 AND 0.3001;
 
-- Comparing different numeric types (implicit coercion)
SELECT * FROM products WHERE id = '100';            -- String coerced to integer
-- WARNING: This may cause index to not be used, or unexpected matches
 
-- Avoid implicit coercion
SELECT * FROM products WHERE id = CAST('100' AS INTEGER);

Date and Time Comparisons:

datetime_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- DATE comparisons (day precision)
SELECT * FROM orders WHERE order_date = '2024-06-15';
 
-- TIMESTAMP comparisons (includes time component)
SELECT * FROM events WHERE event_time = '2024-06-15 14:30:00';
 
-- Beware: DATE vs TIMESTAMP mismatch
-- If order_date is TIMESTAMP, this may miss orders:
SELECT * FROM orders WHERE order_date = '2024-06-15';
-- The literal '2024-06-15' becomes '2024-06-15 00:00:00'
-- So 2024-06-15 14:30:00 won't match!
 
-- Safe pattern for selecting a specific day from TIMESTAMP columns:
SELECT * FROM orders 
WHERE order_date >= '2024-06-15' 
  AND order_date < '2024-06-16';
 
-- Or truncate the timestamp:
SELECT * FROM orders WHERE DATE(order_date) = '2024-06-15';  -- MySQL
SELECT * FROM orders WHERE order_date::DATE = '2024-06-15'; -- PostgreSQL
-- WARNING: Truncation prevents index usage
 
-- Time zone considerations
SELECT * FROM events 
WHERE event_time AT TIME ZONE 'America/New_York' >= '2024-06-15 09:00:00';

The Timestamp Truncation Pattern

Boolean Comparisons:

boolean_comparisons.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Explicit comparison
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active = FALSE;
 
-- Implicit comparison (PostgreSQL, MySQL)
SELECT * FROM users WHERE is_active;          -- Equivalent to = TRUE
SELECT * FROM users WHERE NOT is_active;      -- Equivalent to = FALSE
 
-- IS TRUE, IS FALSE, IS UNKNOWN (SQL standard)
SELECT * FROM users WHERE is_verified IS TRUE;    -- Only TRUE (excludes NULL)
SELECT * FROM users WHERE is_verified IS FALSE;   -- Only FALSE (excludes NULL)
SELECT * FROM users WHERE is_verified IS NOT TRUE; -- FALSE or NULL
 
-- Three-state boolean handling
-- If is_verified can be TRUE, FALSE, or NULL (unknown):
SELECT COUNT(*) AS verified FROM users WHERE is_verified IS TRUE;
SELECT COUNT(*) AS unverified FROM users WHERE is_verified IS FALSE;
SELECT COUNT(*) AS unknown FROM users WHERE is_verified IS NULL;

Comparison Operator Precedence

When combining comparison operators with logical operators (AND, OR, NOT), precedence determines evaluation order. Misunderstanding precedence is a common source of query bugs.

Standard SQL Operator Precedence (highest to lowest):

SQL Operator Precedence
Precedence	Operators	Description
1 (highest)	Parentheses `()`	Explicit grouping
2	Unary `+`, `-`, `~`	Sign, bitwise NOT
3	`*`, `/`, `%`	Multiplication, division, modulo
4	`+`, `-`	Addition, subtraction
5	Comparison `=`, `<>`, `<`, `>`, `<=`, `>=`	All comparison operators
6	`NOT`	Logical negation
7	`AND`	Logical conjunction
8 (lowest)	`OR`	Logical disjunction

precedence_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Precedence trap: AND binds tighter than OR
-- WRONG interpretation: Find active users who are admins or moderators
SELECT * FROM users WHERE role = 'admin' OR role = 'moderator' AND is_active = TRUE;
-- This is parsed as: role = 'admin' OR (role = 'moderator' AND is_active = TRUE)
-- Returns: ALL admins (even inactive) plus active moderators
 
-- CORRECT: Use parentheses to clarify intent
SELECT * FROM users WHERE (role = 'admin' OR role = 'moderator') AND is_active = TRUE;
-- Returns: Active admins and active moderators
 
-- Another common mistake
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' AND priority > 5;
-- Parsed as: status = 'pending' OR (status = 'processing' AND priority > 5)
-- Gets ALL pending orders plus high-priority processing orders
 
-- What you probably meant:
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing') AND priority > 5;
-- Gets high-priority orders that are pending or processing
 
-- When in doubt, add parentheses
-- Explicit is better than relying on precedence memory

The Parentheses Rule

Performance Implications of Comparison Choices

Different comparison patterns have dramatically different performance characteristics. Understanding these enables you to write queries that scale.

Index-Friendly Comparisons:

Indexable Patterns

•column = value (equality)
•column > value (range)
•column >= value AND column < value (bounded range)
•column IN (v1, v2, v3) (multiple equality)
•column IS NULL
•column LIKE 'prefix%' (trailing wildcard)

Non-Indexable Patterns

•LOWER(column) = value (function on column)
•column + 1 = value (arithmetic on column)
•column <> value (inequality often scans all)
•column LIKE '%suffix' (leading wildcard)
•column1 = column2 (variable both sides)
•CAST(column AS type) = value (type conversion)

performance_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Verify index usage with EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 1234;
-- Look for: Index Seek, Index Scan vs. Table Scan
 
-- Transform non-sargable to sargable
-- Instead of:
SELECT * FROM events WHERE YEAR(event_date) = 2024;
-- Use:
SELECT * FROM events 
WHERE event_date >= '2024-01-01' AND event_date < '2025-01-01';
 
-- Instead of:
SELECT * FROM products WHERE price * 1.1 < 100;
-- Use:
SELECT * FROM products WHERE price < 100 / 1.1;
 
-- Instead of:
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
-- Use (if functional index exists):
SELECT * FROM users WHERE email_lower = 'john@example.com';  -- computed column
-- Or create a functional index on LOWER(email)
 
-- Composite index considerations
-- Index on (status, created_at) works for:
SELECT * FROM orders WHERE status = 'pending';                      -- Yes
SELECT * FROM orders WHERE status = 'pending' AND created_at > x;   -- Yes
SELECT * FROM orders WHERE created_at > x;                          -- Limited benefit
-- The leftmost columns of a composite index must be in the query

The Optimizer's Judgment

Summary: Mastering Comparison Operators

Comparison operators are the foundation of SQL filtering. Their correct use determines not just whether your queries return correct results, but whether they perform acceptably at scale.

Key Takeaways

•Equality comparisons are type and collation sensitive — Case sensitivity and encoding affect results
•Inequality excludes NULL rows — Always explicitly handle NULL when using <>
•Relational operators enable range queries — These are highly indexable and efficient
•Use IS NULL and IS DISTINCT FROM for NULL handling — Standard comparison operators fail with NULL
•Operator precedence matters — AND binds tighter than OR; use parentheses for clarity
•Data type affects comparison behavior — Floating-point, datetime, and boolean types have nuances
•Sargability determines performance — Keep indexed columns 'naked' in comparisons

What's Next:

Page Complete

2 / 5