Compare Values with SQL Functions and Operators - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL supports the comparison operators, predicates, and functions of PostgreSQL. For the full PostgreSQL 12 reference, see Comparison Functions and Operators.

Comparison operators

Comparison operators compare two values and return TRUE or FALSE. Use them in WHERE clauses, HAVING clauses, conditional expressions, and anywhere a Boolean result is needed.

Operator	Syntax	Description	Example	Result
`<`	`a < b`	Less than	`3 < 5`	`TRUE`
`>`	`a > b`	Greater than	`5 > 3`	`TRUE`
`<=`	`a <= b`	Less than or equal to	`3 <= 3`	`TRUE`
`>=`	`a >= b`	Greater than or equal to	`5 >= 6`	`FALSE`
`=`	`a = b`	Equal to	`NULL = NULL`	`NULL`
`<>` or `!=`	`a <> b`	Not equal to	`2 <> 2`	`FALSE`

The standard = and <> operators return NULL when either operand is NULL. Use IS DISTINCT FROM and IS NOT DISTINCT FROM for NULL-safe comparisons.

Comparison predicates

Comparison predicates perform structured tests that must follow SQL standard syntax. Unlike operators, predicates can handle NULL values explicitly.

BETWEEN

BETWEEN checks whether a value falls within an inclusive range:

value BETWEEN min AND max
-- Equivalent to: value >= min AND value <= max

Examples:

-- Numeric range
SELECT 3 BETWEEN 2 AND 6;     -- TRUE
SELECT 7 BETWEEN 2 AND 6;     -- FALSE

-- String range (lexicographic order)
SELECT 'Mar' BETWEEN 'Jan' AND 'Sep';  -- TRUE

-- NULL propagation
SELECT NULL BETWEEN 2 AND 4;   -- NULL
SELECT 2 BETWEEN NULL AND 6;   -- NULL
SELECT 8 BETWEEN NULL AND 6;   -- FALSE

Use NOT BETWEEN to negate the test:

SELECT 7 NOT BETWEEN 2 AND 6;  -- TRUE

BETWEEN SYMMETRIC

BETWEEN SYMMETRIC works like BETWEEN but automatically swaps min and max if they are given out of order, so the range is never empty:

-- Standard BETWEEN: 6 BETWEEN 10 AND 2 → FALSE (empty range)
-- BETWEEN SYMMETRIC: 6 BETWEEN SYMMETRIC 10 AND 2 → TRUE (same as 6 BETWEEN 2 AND 10)
SELECT 6 BETWEEN SYMMETRIC 10 AND 2;  -- TRUE

Use NOT BETWEEN SYMMETRIC to negate the test.

IS DISTINCT FROM and IS NOT DISTINCT FROM

These predicates treat NULL as an ordinary comparable value rather than an unknown. Use them when columns may contain NULL and you need deterministic results.

Truth table — how results differ from standard `=` and `<>`:

a	b	`a = b`	`a <> b`	`a IS DISTINCT FROM b`	`a IS NOT DISTINCT FROM b`
1	1	TRUE	FALSE	FALSE	TRUE
1	2	FALSE	TRUE	TRUE	FALSE
1	NULL	NULL	NULL	TRUE	FALSE
NULL	NULL	NULL	NULL	FALSE	TRUE

-- Standard equality: NULL comparisons return NULL
SELECT 1 = NULL;           -- NULL
SELECT NULL = NULL;        -- NULL

-- NULL-safe equality: deterministic results
SELECT 1 IS NOT DISTINCT FROM NULL;    -- FALSE
SELECT NULL IS NOT DISTINCT FROM NULL; -- TRUE

IS NULL and IS NOT NULL

Test whether an expression evaluates to NULL:

SELECT NULL IS NULL;       -- TRUE
SELECT 1 IS NULL;          -- FALSE
SELECT NULL IS NOT NULL;   -- FALSE
SELECT 1 IS NOT NULL;      -- TRUE

ISNULL and NOTNULL are non-standard aliases for IS NULL and IS NOT NULL. Avoid them in portable SQL.

Boolean predicates

Test whether a Boolean expression has a specific three-valued logic state (TRUE, FALSE, or UNKNOWN):

Predicate	Description
`boolean_expression IS TRUE`	TRUE only if the expression is TRUE
`boolean_expression IS NOT TRUE`	TRUE if the expression is FALSE or UNKNOWN
`boolean_expression IS FALSE`	TRUE only if the expression is FALSE
`boolean_expression IS NOT FALSE`	TRUE if the expression is TRUE or UNKNOWN
`boolean_expression IS UNKNOWN`	TRUE only if the expression is UNKNOWN (NULL)
`boolean_expression IS NOT UNKNOWN`	TRUE if the expression is TRUE or FALSE

SELECT TRUE IS TRUE;        -- TRUE
SELECT NULL IS UNKNOWN;     -- TRUE
SELECT NULL IS NOT TRUE;    -- TRUE

Comparison functions

num_nonnulls and num_nulls

These functions count non-NULL and NULL values across a variable argument list. Use them for data quality checks — for example, to count how many columns in a row are populated.

Function	Description	Example	Result
`num_nonnulls(VARIADIC "any")`	Returns the number of non-NULL arguments.	`num_nonnulls(1, NULL, 2)`	`2`
`num_nulls(VARIADIC "any")`	Returns the number of NULL arguments.	`num_nulls(1, NULL, 2)`	`1`

-- Count how many of five fields are populated in a row
SELECT num_nonnulls(col1, col2, col3, col4, col5) AS populated_count
FROM my_table;

-- Identify rows where more than two fields are NULL
SELECT *
FROM my_table
WHERE num_nulls(col1, col2, col3, col4, col5) > 2;