Comparison Operators in SQL

Introduction

Comparison operators evaluate the relationship between two SQL expressions. They answer questions such as whether a price equals a target, a balance exceeds a limit, an order date falls before a deadline, or two columns disagree. The resulting condition is used to filter rows, enforce constraints, choose CASE branches, connect tables, and control data changes.

SELECT product_id, product_name, price
FROM products
WHERE price >= 100.00;

The condition compares each product price with 100.00. Rows for which the condition is TRUE are returned. That description is simple, but production behavior depends on boundaries, NULL, data types, collation, time zones, implicit conversion, operator precedence, and indexes. Correct comparison logic therefore requires more than memorizing symbols.

Main SQL Comparison Operators

OperatorMeaningExample
=Equal tostatus = 'PAID'
<>Not equal tostatus <> 'CANCELLED'
!=Not equal to in many systemsstatus != 'CANCELLED'
>Greater thanamount > 500
>=Greater than or equal toquantity >= 1
<Less thanstock < 10
<=Less than or equal toscore <= 100

The standard not-equal operator is <>. Many database systems also accept !=, but portable SQL generally prefers <>. Operators such as BETWEEN, IN, LIKE, and IS NULL are related predicates with specialized semantics; they complement rather than replace the basic comparison operators.

Equality with Numbers, Text, and Dates

The equality operator returns TRUE when comparable expressions represent the same value. Numeric equality is common for identifiers, quantities, and exact amounts. Text equality is used for codes and controlled statuses. Date equality matches one calendar date, while timestamp equality matches one precise point at the stored resolution.

SELECT order_id, total_amount
FROM orders
WHERE customer_id = :customer_id
  AND status = 'PAID'
  AND order_date = DATE '2026-11-01';

Use typed parameters or typed literals supported by the database. A string that looks like a date is not necessarily interpreted consistently across session settings and products. Comparing a timestamp to midnight does not mean "during this day"; it means exactly midnight.

Text Equality and Collation

Text equality is governed by character type and collation. A collation can determine whether uppercase and lowercase letters compare as equal, how accents are treated, and which language-specific ordering rules apply. Consequently, 'Admin' may equal 'admin' in one column but not another.

Do not assume case behavior from one development database. Define the intended semantics for usernames, email addresses, country codes, product SKUs, and display text. Normalize business identifiers deliberately and enforce uniqueness using a compatible collation or normalized key. Presentation text often needs linguistic collation, while tokens and security-sensitive identifiers may require exact binary-like comparison.

Trailing Spaces and Normalization

CHAR values are commonly padded to a fixed width, and database products differ in how trailing spaces affect comparison. User input can also contain leading spaces, non-breaking spaces, or Unicode variants that look alike. Applying TRIM or LOWER everywhere in ad hoc queries may hide a weak data model and prevent efficient index use.

Normalize input at a defined boundary, store a canonical form when the business requires one, and preserve a separate display form if necessary. Test whitespace and character variants explicitly rather than assuming equality means visual similarity.

Not Equal and the Missing NULL Rows

The not-equal operator selects known values that differ from the comparison value. It does not automatically include NULL because NULL represents missing or unknown information. The following query omits both CANCELLED rows and rows whose status is NULL.

SELECT order_id, status
FROM orders
WHERE status <> 'CANCELLED';

If the business rule considers a missing status to be "not cancelled," state that explicitly.

WHERE status <> 'CANCELLED'
   OR status IS NULL

Whether NULL should qualify is a domain decision. Adding it mechanically can be as incorrect as forgetting it.

Greater Than and Greater Than or Equal To

The > operator excludes the boundary; >= includes it. This distinction matters for age limits, minimum balances, approval thresholds, dates, and pagination cursors. If a discount applies to orders of at least 500.00, the correct predicate is total_amount >= 500.00. Using > incorrectly rejects an order worth exactly 500.00.

Translate the requirement into plain words before choosing the operator. Phrases such as "more than," "after," and "older than" usually indicate an exclusive boundary. "At least," "on or after," and "minimum" usually indicate an inclusive boundary. Ambiguous requirements need clarification, not a guessed symbol.

Less Than and Less Than or Equal To

The < operator excludes its boundary, while <= includes it. Low-stock alerts often use available_quantity < reorder_level. A score rule allowing values up to 100 uses score <= 100. For expiration, expires_at <= CURRENT_TIMESTAMP means an item expiring exactly now is no longer valid.

PredicateBoundary included?Typical wording
value > 10NoMore than 10
value >= 10YesAt least 10
value < 10NoLess than 10
value <= 10YesAt most 10

SQL Uses Three-Valued Logic

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. Comparisons involving NULL normally produce UNKNOWN because the database cannot determine whether an unknown value equals, exceeds, or differs from another value. WHERE and HAVING retain only TRUE rows; both FALSE and UNKNOWN are filtered out.

-- Incorrect: both expressions evaluate to UNKNOWN.
WHERE manager_id = NULL
WHERE manager_id <> NULL

-- Correct NULL predicates.
WHERE manager_id IS NULL
WHERE manager_id IS NOT NULL

This is one of the most important differences between SQL and ordinary two-valued programming logic. NULL is not zero, an empty string, or a special value that equals itself.

NULL with Logical Operators

UNKNOWN interacts with AND, OR, and NOT according to three-valued logic. FALSE AND UNKNOWN is FALSE because one false condition is enough. TRUE AND UNKNOWN remains UNKNOWN. TRUE OR UNKNOWN is TRUE, while FALSE OR UNKNOWN remains UNKNOWN. NOT UNKNOWN is still UNKNOWN.

These rules explain why a negated condition does not necessarily return every row excluded by the original condition. When nullable columns participate in business logic, write explicit NULL behavior and test it. Adding NOT around a predicate is not always equivalent to constructing the business opposite.

Combining Comparisons with AND and OR

AND requires all conditions to be TRUE. OR requires at least one condition to be TRUE. SQL generally evaluates AND before OR, so parentheses are essential when the intended grouping differs or when readability matters.

SELECT order_id, status, total_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND (status = 'PAID' OR status = 'SHIPPED')
  AND total_amount >= :minimum_amount;

Without parentheses, a tenant condition may apply to only one OR branch and expose another tenant's data. Security predicates deserve explicit grouping even when default precedence happens to produce the same result today.

Comparing One Column with Another

Both sides of a comparison can be columns or expressions. Cross-column predicates are useful for finding invalid periods, price changes, mismatched totals, and values outside row-specific limits.

SELECT promotion_id, starts_at, ends_at
FROM promotions
WHERE ends_at <= starts_at;

The same invariant can often become a CHECK constraint so every writer must obey it.

CHECK (ends_at > starts_at)

Remember that a CHECK generally accepts UNKNOWN. If either endpoint must be present, define NOT NULL as well as the comparison constraint.

Comparisons in JOIN Conditions

Equality joins connect related keys and are the most common join form. A non-equality join can map a value into a range, associate an event with an effective-dated record, or find overlapping intervals.

SELECT e.employee_id, b.band_name
FROM employees AS e
JOIN salary_bands AS b
  ON e.salary >= b.minimum_salary
 AND e.salary <  b.maximum_salary;

The half-open interval includes the lower bound and excludes the upper bound. Adjacent salary bands can meet without overlapping. Range joins need careful data constraints and indexes because they can be more expensive than key equality joins.

Comparisons in HAVING and CASE

WHERE filters input rows before grouping, while HAVING filters groups after aggregation. A comparison against COUNT, SUM, or AVG therefore belongs in HAVING.

SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
HAVING SUM(total_amount) >= 10000.00;

CASE evaluates conditions in order and returns the first matching branch. Put narrower or higher thresholds before broader ones; otherwise an early condition can make later branches unreachable.

CASE
  WHEN score >= 90 THEN 'Excellent'
  WHEN score >= 75 THEN 'Good'
  WHEN score >= 60 THEN 'Pass'
  ELSE 'Review'
END

Comparisons in UPDATE and DELETE

In a data-changing statement, the predicate determines the mutation scope. Include identity, ownership, current state, and any concurrency expectation required by the operation. Always validate the affected-row count.

UPDATE orders
SET status = 'CANCELLED',
    cancelled_at = CURRENT_TIMESTAMP
WHERE order_id = :order_id
  AND customer_id = :authenticated_customer_id
  AND status = 'PENDING';

Zero rows can mean missing, unauthorized, or no longer pending. More rows than expected indicate an unsafe identity assumption. Preview broad DELETE predicates with SELECT and count the target before execution.

Exact and Approximate Numeric Comparisons

Integers and DECIMAL or NUMERIC values are appropriate for exact business comparisons. Money should normally use a fixed-precision type with documented currency and rounding rules. Numeric data stored as text compares lexicographically, so values such as '100' and '20' can sort or compare unexpectedly.

Floating-point types represent many decimal values approximately. A calculation expected to produce 0.3 may not compare exactly equal to a stored 0.3 representation. For scientific measurements, compare within a documented tolerance.

WHERE ABS(measured_value - :expected_value) < :tolerance

The tolerance must come from domain accuracy requirements. It should not be an arbitrary workaround for choosing the wrong type.

Date and Timestamp Comparisons

DATE represents a calendar date; TIMESTAMP represents a date and time, with time-zone behavior varying by type and database. Use typed values and define whether boundaries are inclusive. To retrieve one day from a timestamp, a half-open range is robust.

WHERE created_at >= :day_start
  AND created_at <  :next_day_start

This pattern includes every supported fractional second in the day and avoids guessing an artificial last moment such as 23:59:59.999. It also remains clear when timestamp precision changes. Compute boundaries using the business time zone, then compare values in the storage convention, commonly UTC.

Active Windows and Effective-Dated Data

Time windows often use a start-inclusive, end-exclusive convention. A promotion active now can be represented as follows:

WHERE valid_from <= CURRENT_TIMESTAMP
  AND (valid_to > CURRENT_TIMESTAMP OR valid_to IS NULL)

Open-ended validity uses NULL intentionally. Effective-dated tables may contain several versions of a business record, so the query must compare the requested time with both boundaries and ensure versions do not overlap. The same design applies to prices, assignments, policies, and slowly changing dimensions.

Comparison versus BETWEEN, IN, and LIKE

BETWEEN is inclusive on both ends and is convenient for closed ranges. Explicit >= and < predicates are often clearer for timestamp windows. IN compares one expression with a set of values and is clearer than a long OR chain. LIKE performs pattern matching rather than exact equality.

WHERE status IN ('PAID', 'SHIPPED')

WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-12-31'

WHERE product_name LIKE :name_pattern

NULL still requires deliberate handling. In particular, NOT IN can produce surprising UNKNOWN results if its list or subquery contains NULL. NOT EXISTS is often safer for anti-matching relationships.

Subqueries, ANY, and ALL

A scalar comparison subquery must return at most one value. If multiple values are possible, use IN, EXISTS, ANY, ALL, or an appropriate join according to the intended logic.

-- Greater than every salary in department 20.
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 20
)

-- Greater than at least one salary in department 20.
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department_id = 20
)

Empty sets and NULL values affect quantified comparisons, so test those cases. EXISTS often expresses relationship existence more directly and avoids comparing selected values.

Data Types and Implicit Conversion

The database must bring both expressions to compatible types. Implicit conversion can change values, raise an error, produce product-specific behavior, or prevent an index from being used. Passing a text parameter for a numeric identifier makes correctness and performance dependent on conversion rules.

Bind parameters using the column's actual type. Store numbers as numeric types and dates as date or timestamp types. If conversion is unavoidable, validate it explicitly at the ingestion boundary rather than repeatedly casting the indexed column in operational queries.

Sargability and Index Use

A sargable predicate allows the optimizer to use an index to locate a range of rows. Direct comparisons on indexed columns are often sargable. Wrapping the column in a function can force calculation for many rows.

-- Often less index-friendly.
WHERE CAST(created_at AS DATE) = :target_date

-- Usually index-friendly.
WHERE created_at >= :day_start
  AND created_at <  :next_day_start

Equality can be highly selective for unique keys. Range predicates can also use ordered indexes. Not-equal predicates frequently match a large portion of a table, so a scan may be cheaper. An index is not automatically beneficial merely because its column appears in a comparison.

Composite Indexes and Comparison Order

A composite index is most effective when its leading columns match common equality predicates, followed by a range or ordering column. For tenant order history, an index on (tenant_id, customer_id, created_at) can support equality on tenant and customer plus a created_at range.

Once a broad range is introduced, later index columns may contribute less to locating rows, although they can still help covering or filtering. Index design must follow real query patterns, data distribution, and execution plans rather than a universal formula.

Comparison Selectivity and Statistics

The optimizer estimates how many rows a condition will match using statistics. Equality on a unique key should return at most one row, while status <> 'ARCHIVED' may return nearly everything. Stale or coarse statistics can lead to poor join order and access choices.

Parameter values can also produce different selectivity. A plan efficient for a rare tenant or date range may be poor for a large one. Investigate actual execution plans, row estimates, parameter sensitivity, and partition pruning when an otherwise simple comparison performs unpredictably.

Security and Tenant Comparisons

Comparison predicates are part of authorization. A record ID comparison alone may identify the row but not prove the caller may access it. Add tenant and ownership predicates from authenticated server context.

SELECT document_id, title, body
FROM documents
WHERE document_id = :document_id
  AND tenant_id = :authenticated_tenant_id
  AND owner_id = :authenticated_user_id;

Parameterization prevents value injection, but it does not create authorization by itself. Never accept tenant_id or owner_id from a request and assume equality with that submitted value is secure. Derive security scope from trusted identity and apply it consistently to SELECT, UPDATE, and DELETE.

Comparisons for Optimistic Locking

An expected version comparison prevents stale writes. The client reads version 7 and submits it with an update. The statement changes the row only if version 7 is still current, then increments the version.

UPDATE documents
SET body = :body,
    version_no = version_no + 1
WHERE document_id = :document_id
  AND version_no = :expected_version;

Zero affected rows indicates a conflict or missing target, not success. Similar current-state comparisons protect workflow transitions and atomic inventory deductions.

Keyset Pagination Uses Comparisons

Keyset pagination continues after the last value seen rather than skipping an OFFSET. With descending created_at and order_id, the next page selects rows whose ordered tuple is lower than the cursor.

WHERE tenant_id = :tenant_id
  AND (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST :page_size ROWS ONLY;

Databases without row-value comparison can express the same logic with carefully grouped OR conditions. The unique tie-breaker prevents duplicate or skipped rows when timestamps are equal.

Boundary Testing

Comparison defects frequently occur at boundaries. For a threshold of 100.00, test 99.99, 100.00, and 100.01 using the actual column precision. For dates, test immediately before, exactly at, and immediately after the start and end. Include NULL, zero, negatives, maximum values, and values rounded to storage precision.

Do not test only representative middle values. A rule can appear correct for 150 while failing the only disputed case at exactly 100. Test both rows that should qualify and rows that should not, and verify the business wording matches the selected inclusive or exclusive operator.

Testing Text and NULL Behavior

Text comparison tests should cover case, accents, trailing spaces, empty strings, Unicode normalization, and the configured collation. Identifier tests should include visually similar but distinct characters where security matters. Verify behavior in the same database configuration used in production.

For nullable columns, test known matching, known nonmatching, and NULL values. Confirm the result for the predicate and its negation. Test NOT IN with nullable data, CHECK constraints that can evaluate to UNKNOWN, and outer joins where unmatched columns become NULL.

Boolean and Status Comparisons

Boolean support differs among database products. PostgreSQL has a native BOOLEAN type, while other systems commonly use BIT, numeric flags, or constrained character values. Write predicates that match the schema and project conventions. A native boolean can often be tested with is_active = TRUE; a numeric flag may use is_active = 1. Do not mix representations casually or depend on implicit conversion from words such as "yes" and "no."

A boolean flag is suitable for a genuine two-state fact. It is not a good replacement for a lifecycle with PENDING, APPROVED, REJECTED, SUSPENDED, and EXPIRED states. Multiple loosely related flags can create impossible combinations such as approved and rejected simultaneously. A constrained status column or related state table provides clearer comparisons and stronger integrity.

Nullable booleans introduce a third stored state in addition to SQL's three-valued evaluation. Decide whether NULL means unknown, not yet assessed, or invalid data. If the business permits only true and false, define NOT NULL and a default where appropriate rather than adding NULL branches to every query.

UUID, Binary, JSON, and Specialized Types

UUID values should be stored in a native UUID type when available, or in a consistent binary or canonical character representation. Comparing normalized native UUID values avoids case, hyphen, and formatting ambiguity. UUID ordering is usually not a meaningful creation order, so a greater-than comparison should not be used as a substitute for a timestamp unless the chosen UUID version and business design explicitly support that behavior.

Binary equality compares byte sequences, not human-readable representations. Hashes, signatures, encrypted values, and files have domain-specific comparison rules; encrypted versions of identical plaintext may intentionally differ. Security-sensitive equality can also require constant-time comparison in the application rather than ordinary SQL comparison, depending on the threat model.

JSON comparison behavior varies substantially by database and JSON type. Comparing a complete JSON document can be sensitive to structural representation, key order, duplicate keys, number normalization, or whether the column stores text versus a parsed binary form. Production queries normally extract a typed JSON property and compare it as the intended number, text, boolean, or timestamp. Frequently filtered properties may deserve generated columns, expression indexes, or relational columns with constraints.

Logical Query Processing and Aliases

SQL is written in one order but conceptually processed in another. FROM and JOIN establish the row source, WHERE filters rows, GROUP BY forms groups, HAVING filters groups, SELECT computes the projection, and ORDER BY sorts the result. This explains why a SELECT alias is usually unavailable in WHERE: the alias has not been produced at that logical stage.

-- The alias generally cannot be used in WHERE at the same level.
SELECT quantity * unit_price AS line_total
FROM order_items
WHERE quantity * unit_price >= :minimum_total;

If a calculated comparison is complex or reused, place it in a common table expression or derived table and compare the exposed alias in the outer query. Do not move a row-level predicate from WHERE to HAVING simply to access an alias. That can change semantics and force unnecessary grouping.

DISTINCT is also applied after row filtering and projection. It removes duplicate projected rows; it does not repair an incorrect comparison or join. If a filter unexpectedly creates duplicates, identify the result grain and relationship first. Applying DISTINCT without understanding why duplicates exist can hide a data or query design defect.

Partitions, Pruning, and Large Tables

Partitioned tables divide data using a key such as event date, tenant, region, or identifier range. A direct comparison on the partition key can let the optimizer prune irrelevant partitions. Wrapping that key in a function or comparing it through an incompatible type may prevent pruning and cause the query to inspect much more data.

-- Clear partition boundaries for a monthly event query.
WHERE event_time >= TIMESTAMP '2026-11-01 00:00:00'
  AND event_time <  TIMESTAMP '2026-12-01 00:00:00'

The partition definition and query must use compatible boundary semantics. A monthly range should not overlap its neighbor or leave a gap. Test boundary records exactly at midnight and inspect the execution plan to confirm pruning; syntactic similarity alone does not prove it occurred.

Large-table comparisons also benefit from bounded result sets and realistic statistics. A correct predicate that returns hundreds of millions of rows can overload memory, network, application processing, or downstream exports even when the database finds those rows efficiently. Performance design includes result volume, not only index access.

Choosing Operators from Business Language

Reliable operator selection starts with a precise requirement. "Adult customer" is incomplete until the jurisdiction, reference date, birth-date interpretation, and exact threshold are known. "Recent orders" needs a time zone and duration. "Different address" needs normalization and a definition of meaningful difference. SQL cannot resolve ambiguous policy.

Write examples before the query. For a free-shipping threshold of 50.00, state whether 49.99 fails, 50.00 passes, and 50.01 passes. For a cancellation deadline, state whether a request exactly at the deadline is accepted. Those examples expose whether the boundary is inclusive and provide direct tests.

Prefer comparing stored facts to explicit values over embedding formatted output or presentation labels in business logic. Compare DECIMAL amount to DECIMAL threshold, DATE to DATE, and status key to status key. Formatting belongs after selection. This separation keeps comparisons type-correct, index-friendly, and stable across languages and user interfaces.

Common Comparison Mistakes

  • Using = NULL or <> NULL instead of IS NULL predicates.
  • Expecting not-equal to include rows containing NULL.
  • Choosing > when the business boundary is inclusive, or >= when it is exclusive.
  • Comparing a timestamp with one midnight value to retrieve a complete day.
  • Comparing numeric or date information stored in text columns.
  • Assuming text comparison is always case-sensitive or case-insensitive.
  • Using a chained expression such as 1 < score < 10 instead of two predicates.
  • Combining AND and OR without parentheses around the intended logic.
  • Testing approximate floating-point values with exact equality.
  • Applying functions or casts to indexed columns unnecessarily.
  • Trusting a record ID comparison without tenant or ownership scope.
  • Ignoring affected-row counts in conditional UPDATE and DELETE statements.

Practical Query Examples

-- High-value completed orders.
SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'COMPLETED'
  AND total_amount >= 1000.00;

-- Low inventory requiring action.
SELECT product_id, available_quantity, reorder_level
FROM products
WHERE available_quantity <= reorder_level;

-- Overdue unpaid invoices.
SELECT invoice_id, due_date, balance_due
FROM invoices
WHERE due_date < CURRENT_DATE
  AND balance_due > 0;

-- Invalid periods requiring cleanup.
SELECT period_id, valid_from, valid_to
FROM policy_periods
WHERE valid_to IS NOT NULL
  AND valid_to <= valid_from;

Each example combines comparisons with domain state. The predicate should be reviewed against NULL rules, storage precision, time-zone policy, and expected indexes before production use.

Comparison Review Checklist

  1. Translate the requirement into explicit inclusive or exclusive boundaries.
  2. Confirm both expressions use compatible, intentional data types.
  3. Define how NULL should behave instead of relying on intuition.
  4. Check text collation, normalization, case, accent, and whitespace rules.
  5. Use half-open ranges for timestamp windows when appropriate.
  6. Group mixed AND and OR conditions with clear parentheses.
  7. Include trusted tenant and ownership predicates for secured records.
  8. Avoid unnecessary functions or casts on indexed columns.
  9. Test values below, at, and above every important boundary.
  10. Inspect execution plans and row estimates for high-volume queries.
  11. Validate affected-row counts for conditional mutations.
  12. Document product-specific behavior when portability is not possible.

Interview Questions and Answers

What are SQL comparison operators? Operators that evaluate relationships such as equality, inequality, greater than, or less than between compatible expressions.

What is the standard not-equal operator? <> is standard SQL; many products also support !=.

Why does column = NULL not work? A comparison with NULL evaluates to UNKNOWN, so IS NULL is required.

Does status <> 'X' include NULL? No. NULL produces UNKNOWN and must be included explicitly if the business rule requires it.

What is a half-open date range? A range that includes its start and excludes its end, commonly written with >= start and < next boundary.

Why can text equality differ across databases? Character types and collations control case, accent, language, and sometimes space behavior.

What is sargability? The ability of a predicate to support efficient index-based search rather than requiring calculation over many rows.

Why is floating-point equality risky? Many decimal values cannot be represented exactly in binary floating-point storage.

How do comparisons protect concurrent updates? Expected version or current-state predicates make the change conditional on the state originally observed.

How should comparison boundaries be tested? With values immediately below, exactly at, and immediately above the boundary, plus NULL and type-specific edge cases.

Key Takeaway

SQL comparison operators are small symbols with large consequences. Their correctness depends on domain boundaries, NULL and three-valued logic, data types, collation, precision, time semantics, logical grouping, security scope, and concurrency assumptions. A predicate that looks obvious can still omit valid rows, include unauthorized data, or prevent efficient access.

RELIABLE COMPARISON = CORRECT OPERATOR
                      + EXPLICIT BOUNDARY
                      + TYPE-CORRECT VALUES
                      + DEFINED NULL BEHAVIOR
                      + INTENTIONAL COLLATION
                      + CLEAR LOGICAL GROUPING
                      + SECURITY SCOPE
                      + INDEX-AWARE DESIGN
                      + BOUNDARY TESTS

Write comparisons from precise business language, test all boundaries and unknown values, and verify both the logical result and execution behavior under production-like data.