Skip to main content

Comparison Operators

Comparison operators are used to compare two values and return a boolean result (TRUE, FALSE, or NULL). They are commonly used in WHERE clauses, CASE expressions, and join conditions.

Supported Operators

Operator

Description

Example

=

Equal to

a = b

<>, !=

Not equal to

a <> b

<

Less than

a < b

>

Greater than

a > b

<=

Less than or equal to

a <= b

>=

Greater than or equal to

a >= b

IS DISTINCT FROM

Not equal, treating NULL as a value (Databricks only)

a IS DISTINCT FROM b

IS NOT DISTINCT FROM

Equal, treating NULL as a value (Databricks only)

a IS NOT DISTINCT FROM b

Syntax

expression1 { = | <> | != | < | > | <= | >= } expression2
expression1 IS [NOT] DISTINCT FROM expression2

Returns

  • Returns TRUE if the comparison condition is satisfied.

  • Returns FALSE if the comparison condition is not satisfied.

  • Returns NULL if either operand is NULL (for standard operators).

NULL Handling

  • Standard comparison operators return NULL when either operand is NULL.

  • IS DISTINCT FROM and IS NOT DISTINCT FROM treat NULL as a comparable value.

Examples

Basic Comparisons

-- Example 1: Equality
> SELECT 1 = 1;
true

-- Example 2: IS NULL check
> SELECT 1 IS NULL;
false

> SELECT 1 IS NOT NULL;
true

> SELECT NULL IS NULL;
true

Timestamp Comparisons

-- Example 3: Timestamp equality
> SELECT TIMESTAMP '2005-09-27 12:00:00' = TIMESTAMP '2005-09-27 12:00:00';
true

-- Example 4: Timestamp less than
> SELECT TIMESTAMP '1957-10-04 19:28:00' < TIMESTAMP '1961-04-12 06:07:00';
true

-- Example 5: Timestamp with timezone
> SELECT TIMESTAMP WITH TIME ZONE '2013-07-22 16:24:00 EST' 
        < TIMESTAMP WITH TIME ZONE '2011-04-29 10:48:00 EST';
false

IS DISTINCT FROM (Databricks Only)

-- Example 6: DISTINCT handles NULL differently
> SELECT 1 IS DISTINCT FROM 1;
false

> SELECT 1 IS DISTINCT FROM 0;
true

> SELECT 1 IS DISTINCT FROM NULL;
true

> SELECT NULL IS DISTINCT FROM NULL;
false

String Comparisons (Case-Sensitive)

-- Example 7: String comparisons are case-sensitive
> SELECT 'ab' = 'Ab';
false

> SELECT 'ab' > 'Ab';
true

> SELECT 'CaSe_SenSITive' = 'case_sensitive';
false

Engine Differences

IS DISTINCT FROM

  • Databricks: Fully supported

  • Vertica: Not supported (will throw error: “DistinctExpr not supported”)

NaN Comparisons

  • Databricks: NaN = NaN returns true

  • Vertica: NaN = NaN returns false

See Also