< [less]

All functions > COMPARISON > < [less]

Returns TRUE if the first value is strictly less than the second value

Syntax

expr1 < expr2

expr1 < expr2

Notes

• Both values must be of comparable types
• NULL comparisons always return NULL (three-valued logic)
• For strings, uses lexicographic (dictionary) ordering
• For dates/timestamps, earlier dates are "less than" later dates
• For ARRAY and ROW, ordering is lexicographic / field-by-field when types align
• Nested NULL in either ARRAY/ROW operand makes the comparison NULL (no early-exit past a poisoned field/element)
• On FLOAT/DOUBLE, write .N (e.g. >.2, =.4, BETWEEN.3) for N digits of rounded precision via DECIMAL(38, N) staging
• Any NaN operand makes the comparison NULL
• Infinity, or any finite value too large for DECIMAL(38, N), falls back to a raw float compare — .N rounding is not applied on that path
• ORDER BY uses a separate total sort order; do not assume it matches < when NULLs are present

Related Functions

• LESS_THAN()

Examples

‹ — typical comparisons

SELECT
    -- Numeric strict order
    f1 := 1 < 2,
    -- Lexicographic strings
    f2 := 'apple' < 'banana',
    -- Dates
    f3 := DATE '2024-01-01' < DATE '2024-06-01'
;

‹ — arrays and rows

SELECT
    -- Lexicographic; first difference decides
    f1 := ARRAY(1, 2, 3) < ARRAY(1., 2., 4.),
    -- Shorter prefix can be less when shared elements match
    f2 := ARRAY(1, 2) < ARRAY(1., 2., 3.),
    -- Row field-by-field; integer/decimal may align per field
    f3 := ROW(1 AS a) < ROW(2. AS a)
;

‹ — three-valued logic

SELECT
    -- NULL on the left
    f1 := NULL::BIGINT < 1,
    -- NULL on the right
    f2 := 1 < NULL::BIGINT,
    -- Nested NULL: no lexicographic early-exit
    f3 := ROW(1::BIGINT, NULL::BIGINT) < ROW(2::BIGINT, 0::BIGINT)
;