MSQL Reference – Comparison Operators

The following are the built-in comparison operators available in the MSQL language. They are divided into the following groups:

Basic Comparison Operators - Basic comparisons =, <>, <, >, <=, >=, BETWEEN and IS [NOT] DISTINCT FROM operators.
Value-testing Operators - NULL and truth-value testing operators IS [NOT] NULL, IS [NOT] TRUE and IS [NOT] FALSE.
String Comparison Operators - String LIKE and SIMILAR TO comparisons.
Spatial/Range Predicate Operators - Range and spatial relationship predicates: CONTAINS, COVERS, COVEREDBY, CROSSES, DISJOINT, EQUALS, INTERSECTS, OVERLAPS, TOUCHES, WITHIN, [IMMEDIATELY] PRECEDES/SUCCEEDS.

Basic Comparison Operators

BASIC COMPARISONS

(a: T) = (b: T) -> bool

(a: T) <> (b: T) -> bool

(a: T) != (b: T) -> bool

(a: T) < (b: T) -> bool

(a: T) <= (b: T) -> bool

(a: T) > (b: T) -> bool

(a: T) >= (b: T) -> bool

Returns the result of comparison between two values.

Arguments

Both operands must be of the same type family (e.g. numeric, character). Comparisons of spatial operands are not supported.

Null handling

If any of the operands is NULL, the result of the comparison will be NULL, regardless of the value of the other operand. It means that even NULL = NULL and 7 <> NULL will return NULL instead of true, which, even though consistent with MSQL's three-valued logic, presents a common pitfail for inexperienced SQL users.

Notes

Operator != is just an alias for <>, for users who are more accustomed to writing != for testing non-equality.

Examples

SELECT x = y, x <> y, x < y, x <= y, x > y, x >= y
  FROM (VALUES (4, 7), (-5, -5)) AS t(x, y);

--> +-------+-------+-------+------+-------+-------+
--> |     = |    <> |     < |   <= |     > |    >= |
--> +-------+-------+-------+------+-------+-------+
--> | false |  true |  true | true | false | false |
--> |  true | false | false | true | false |  true |
--> +-------+-------+-------+------+-------+-------+

See also

IS [NOT] DISTINCT FROM

BETWEEN

(x: T) BETWEEN (a: T) AND (b: T) -> bool

(x: T) NOT BETWEEN (a: T) AND (b: T) -> bool

Checks whether the value of x is between given bounds (bounds are inclusive).

Notes

These operators are just a shorthand way of writing the equivalent comparisons by hand:

x     BETWEEN a AND b  <->  x >= a AND x <= b
x NOT BETWEEN a AND b  <->  x < a  OR  x > b

All the issues with NULL values mentioned above when describing basic comparisons apply here as well.

Examples

SELECT 10 BETWEEN 5 AND 100;
  --> true

SELECT 10 NOT BETWEEN 1 AND 10;
  --> false

See also

=, <>, !=, <, <=, >, >=

IS DISTINCT FROM

(a: T) IS DISTINCT FROM (b: T) -> bool

(a: T) IS NOT DISTINCT FROM (b: T) -> bool

(a: T) <=> (b: T) -> bool

Checks whether the two operands have the same value or not.

Notes

For non-null inputs, IS DISTINCT FROM is the same as the <> operator, and IS NOT DISTINCT FROM the same as the operator =.

However, if both inputs are null, IS DISTINCT FROM returns false, and IS NOT DISTINCT FROM returns true.
When only one input is null, IS DISTINCT FROM return true, and IS NOT DISTINCT FROM returns false.
Neither operator ever returns NULL, under any conditions.

The a <=> b is equivalent to a IS NOT DISTINCT FROM b.

These predicates effectively act as if NULL were a normal data value, rather than representing an unknown/missing value.

Examples

SELECT 'abc' IS DISTINCT FROM NULL::text;
  --> true

SELECT NULL::int64 IS NOT DISTINCT FROM NULL::int64;
  --> true

SELECT 'abc' <=> 'abc';
  --> true

See also

IS [NOT] NULL =, <>, !=, <, <=, >, >=

Value-testing Operators

N IS NULL

(x: T) IS NULL -> bool

(x: T) IS NOT NULL -> bool

Checks whether the given operand is equal to NULL.

Notes

The IS NULL and IS NOT NULL operators must be used instead of x = NULL or x <> NULL, which are incorrect because one NULL value is not equal to another NULL value. That is so because NULL represents an unknown/missing value, and it is not known (in the query evaluation context) whether two NULLs represent the same value.

Examples

SELECT NULL IS NULL;
  --> true

SELECT 10 IS NOT NULL;
  --> true

T IS TRUE

(x: bool) IS TRUE -> bool

(x: bool) IS NOT TRUE -> bool

Checks whether a given boolean expression is true.

Notes

These operators always return true or false, never NULL, even when the operand evaluates to NULL.

These operators can be useful for special handling of NULL values in boolean contexts, e.g. in filter or join conditions.

Examples

SELECT false IS TRUE;
  --> false

SELECT NULL IS NOT TRUE;
  --> true

F IS FALSE

(x: bool) IS FALSE -> bool

(x: bool) IS NOT FALSE -> bool

Checks whether a given boolean expression is false.

Notes

These operators always return true or false, never NULL, even when the operand evaluates to NULL.

These operators can be useful for special handling of NULL values in boolean contexts.
For example, NULLs in the WHERE clause behave the same as false (i.e. the rows are dropped), but if this isn't desired, the IS NOT FALSE operator could be applied to fix it.

Examples

SELECT true IS FALSE;
  --> false

SELECT NULL IS NOT FALSE;
  --> true

String Comparison operators

LIKE OPERATOR

(x: text) LIKE (p: text) -> bool

(x: text) NOT LIKE (p: text) -> bool

(x: text) LIKE (p: text) ESCAPE e -> bool

(x: text) NOT LIKE (p: text) ESCAPE e -> bool

Tests whether a character string (first operand) matches a given pattern (second operand).

Null handling

If any of the operands is NULL, the result will also be NULL.

Notes

A pattern can contain arbitrary characters, which all match only themselves (e.g. letter a in the pattern would match exactly one letter a in the string), except for two special characters: underscore _ and percent-sign %. The underscore _ in a pattern stands for any single character, and the percent-sign % matches any sequence of zero or more characters.

To match a literal underscore or percent-sign character instead of a wildcard, the character should be escaped by an escape character (the default is backspace '\'). A different escape character can be chosen by appending the ESCAPE clause to the LIKE operator (e.g. x LIKE 'C:$\Documents$\%' ESCAPE '$'). To match the escape character itself, two escape characters should be used (i.e. '\\').

The backslash (\) itself must be escaped in character string literals (as it also is used for specifying Unicode escape sequences), therefore matching a literal backslash by the LIKE operator (when the default escape character is used) must be done as x LIKE '%\\\\%'.

The character matching is case-sensitive. If a case-insensitive match is needed, it can be accomplished by first converting the input to lowercase (or uppercase) using the LOWER or UPPER functions, e.g. by specifying LOWER(x) LIKE '%stuff%'.

LIKE pattern matching always tries to cover the entire string. Therefore, if just a subsequence of it needs to be matched, the pattern should start and end with a % sign (e.g. finding 'abc' anywhere in the string -> x LIKE '%abc%').

Examples

SELECT s
  FROM (VALUES 'abc', 'ABC', 'abracadabra', 'banana', 'abacus') AS t(s)
  WHERE s LIKE 'ab%';

--> +---------------+
--> |             s |
--> +---------------+
--> |         'abc' |
--> | 'abracadabra' |
--> |      'abacus' |
--> +---------------+

SELECT s
  FROM (VALUES 'asterix', 'boar', 'obelix', 'taxi') AS t(s)
  WHERE s NOT LIKE '%x';

--> +--------+
--> |      s |
--> +--------+
--> | 'boar' |
--> | 'taxi' |
--> +--------+

IN SIMILAR TO OPERATOR

(x: text) SIMILAR TO (p: text) -> bool

(x: text) NOT SIMILAR TO (p: text) -> bool

(x: text) SIMILAR TO (p: text) ESCAPE e -> bool

(x: text) NOT SIMILAR TO (p: text) ESCAPE e -> bool

Tests whether a character string (first operand) matches a given POSIX regular-expression pattern (second operand).

Null handling

If any of the operands is NULL, the result will also be NULL.

Notes

This operator is not yet supported!

Spatial/Range Predicate operators

@> CONTAINS

(v1: T1) @> (v2: T2) -> bool

(v1: T1) CONTAINS (v2: T2) -> bool

Returns true if one range/value wholly contains another range/value. In case of spatial operands, it's an alias for ST_CONTAINS function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

a CONTAINS b is equivalent to b WITHIN a.

Examples

SELECT RANGE(0, 20) @> RANGE(10, 15);
  --> true

SELECT RANGE(0, 20) CONTAINS RANGE(10, 25);
  --> false

SELECT RANGE(0, 20) @> 15;
  --> true

SELECT RANGE(0, 20) CONTAINS 25;
  --> false

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' @> linestring '(5 0, 15 0, 15 10)';
  --> true

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 10 10, 15 5, 5 5))' CONTAINS multipoint '(2 2, 10 8)';
  --> false

#> COVERS

(g1: anygeometry) #> (g2: anygeometry) -> bool

(g1: anygeometry) COVERS (g2: anygeometry) -> bool

Alias for the ST_COVERS function.

Examples

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' COVERS point '(10 0)';
  --> true

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))' #> linestring '(5 0, 10 0, 10 20)';
  --> false

<# COVEREDBY

(g1: anygeometry) <# (g2: anygeometry) -> bool

(g1: anygeometry) COVEREDBY (g2: anygeometry) -> bool

Alias for the ST_COVEREDBY function.

Examples

SELECT point '(10 0)' COVEREDBY polygon '((0 0, 20 0, 20 20, 0 20, 0 0))';
  --> true

SELECT linestring '(5 0, 10 0, 10 20)' <# polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))';
  --> false

-|- CROSSES

(g1: anygeometry) -|- (g2: anygeometry) -> bool

(g1: anygeometry) CROSSES (g2: anygeometry) -> bool

Alias for the ST_CROSSES function.

Examples

SELECT linestring '(0 0, 10 0, 20 10)' CROSSES linestring '(0 10, 20 0)';
  --> true

SELECT multipoint '(10 10, 20 20)' -|- polygon '((0 0, 20 0, 20 20, 0 20, 0 0))';
  --> false

!&& DISJOINT

(v1: T1) !&& (v2: T2) -> bool

(v1: T1) DISJOINT (v2: T2) -> bool

Returns true if the two ranges (or a range and a value) are completely disjoint. In case of spatial operands, it's an alias for ST_DISJOINT function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

a DISJOINT b is equivalent to !(a INTERSECTS b).

Examples

SELECT RANGE(0, 20) !&& RANGE(30, 40);
  --> true

SELECT RANGE(0, 20) DISJOINT RANGE(10, 40);
  --> false

SELECT RANGE(0, 20) !&& 30;
  --> true

SELECT RANGE(0, 20) DISJOINT 10;
  --> false

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' DISJOINT point '(30 40)';
  --> true

SELECT point '(0 0)' !&& linestring '(0 0, 10 10)';
  --> false

~= EQUALS

(v1: T1) ~= (v2: T2) -> bool

(v1: T1) EQUALS (v2: T2) -> bool

Returns true if the two ranges (or a range and a value) contain all the same values. In case of spatial operands, it's an alias for ST_EQUALS function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

The ranges are equal if both their lower bounds and upper bounds are equal, i.e. if LBOUND(r1) = LBOUND(r2) AND RBOUND(r1) = RBOUND(r2).

Examples

SELECT RANGE(10, 20) ~= RANGE(20, 10);
  --> true

SELECT RANGE(10, 20) EQUALS RANGE(30, 10);
  --> false

SELECT RANGE(10, 10) ~= 10;
  --> true

SELECT RANGE(10, 20) EQUALS 10;
  --> false

SELECT linestring '(0 0, 5 5, 10 10)' EQUALS linestring '(10 10, 0 0)';
  --> true

SELECT linestring '(0 0, 20 0, 20 20, 0 20, 0 0)' ~= linestring '(0 0, 20 20, 20 0, 0 20, 0 0)';
  --> false

&& INTERSECTS

(v1: T1) && (v2: T2) -> bool

(v1: T1) INTERSECTS (v2: T2) -> bool

Returns true if the two ranges (or a range and a value) intersect. In case of spatial operands, it's an alias for ST_INTERSECTS function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

a INTERSECTS b is equivalent to !(a DISJOINT b).

Examples

SELECT RANGE(0, 20) && RANGE(10, 40);
  --> true

SELECT RANGE(0, 20) INTERSECTS RANGE(30, 40);
  --> false

SELECT RANGE(0, 20) && 10;
  --> true

SELECT RANGE(0, 20) INTERSECTS 30;
  --> false

SELECT linestring '(0 0, 10 10)' INTERSECTS point '(5 5)';
  --> true

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' && point '(30 40)';
  --> false

<@> OVERLAPS

(v1: T1) <@> (v2: T2) -> bool

(v1: T1) OVERLAPS (v2: T2) -> bool

Returns true if the two ranges overlap, i.e. they intersect and their intersection is not just a single point. In case of spatial operands, it's an alias for ST_OVERLAPS function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

If the arguments are a range and a single value, the result will always be false.

Examples

SELECT RANGE(0, 20) <@> RANGE(10, 40);
  --> true

SELECT RANGE(0, 20) OVERLAPS RANGE(20, 40);
  --> false

SELECT RANGE(0, 20) <@> 10;
  --> false

SELECT RANGE(0, 20) OVERLAPS 20;
  --> false

SELECT linestring '(0 10, 0 0, 10 0, 10 10)' OVERLAPS linestring '(0 -10, 0 0, 10 0, 10 -10)';
  --> true

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' <@> linestring '(10 10, 30 30)';
  --> false

>|< TOUCHES

(v1: T1) >|< (v2: T2) -> bool

(v1: T1) TOUCHES (v2: T2) -> bool

Returns true if the two ranges (or a range and a value) touch at a single point. In case of spatial operands, it's an alias for ST_TOUCHES function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Examples

SELECT RANGE(0, 10) >|< RANGE(10, 20);
  --> true

SELECT RANGE(0, 12) TOUCHES RANGE(10, 20);
  --> false

SELECT RANGE(0, 10) >|< 10;
  --> true

SELECT RANGE(0, 12) TOUCHES 10;
  --> false

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' TOUCHES polygon '((20 5, 30 5, 30 15, 20 15, 20 5))';
  --> true

SELECT polygon '((0 0, 20 0, 20 20, 0 20, 0 0))' >|< linestring '(10 10, 30 30)';
  --> false

<@ WITHIN

(v1: T1) <@ (v2: T2) -> bool

(v1: T1) WITHIN (v2: T2) -> bool

Returns true if one range/value is wholly contained within another range/value. In case of spatial operands, it's an alias for ST_WITHIN function.

Arguments

The arguments must both be geometries or ranges, or a range and a corresponding numeric/timepoint value.

Notes

a WITHIN b is equivalent to b CONTAINS a.

Examples

SELECT RANGE(10, 15) <@ RANGE(0, 20);
  --> true

SELECT RANGE(10, 25) WITHIN RANGE(0, 20);
  --> false

SELECT 15 <@ RANGE(0, 20);
  --> true

SELECT 25 WITHIN RANGE(0, 20);
  --> false

SELECT linestring '(5 0, 15 0, 15 10)' WITHIN polygon '((0 0, 20 0, 20 20, 0 20, 0 0))';
  --> true

SELECT multipoint '(10 0, 0 10, 20 20)' <@ polygon '((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 10 10, 5 5))';
  --> false

<<- PRECEDES

(v1: T1) <<- (v2: T) -> bool

(v1: T1) PRECEDES (v2: T2) -> bool

Returns true if one range/value is completely to the left of another range/value.

Arguments

The arguments must both be ranges, or a range and a corresponding numeric/timepoint value.

Notes

The range r1 is completely on the left of range r2 if it holds that RBOUND(r1) <= LBOUND(r2).

a PRECEDES b is equivalent to b SUCCEEDS a.

Examples

SELECT RANGE(0, 10) <<- RANGE(20, 30);
  --> true

SELECT RANGE(0, 25) PRECEDES RANGE(20, 30);
  --> false

SELECT 10 <<- RANGE(20, 30);
  --> true

SELECT RANGE(0, 25) PRECEDES 20;
  --> false

<<| IMMEDIATELY PRECEDES

(v1: T1) <<| (v2: T) -> bool

(v1: T1) IMMEDIATELY PRECEDES (v2: T2) -> bool

Returns true if one range/value is immediately to the left of another range/value.

Arguments

The arguments must both be ranges, or a range and a corresponding numeric/timepoint value.

Notes

The range r1 is immediately on the left of range r2 if it holds that RBOUND(r1) = LBOUND(r2).

a IMMEDIATELY PRECEDES b is equivalent to b IMMEDIATELY SUCCEEDS a.

Examples

SELECT RANGE(0, 20) <<| RANGE(20, 30);
  --> true

SELECT RANGE(0, 10) IMMEDIATELY PRECEDES RANGE(20, 30);
  --> false

SELECT 20 <<| RANGE(20, 30);
  --> true

SELECT RANGE(0, 10) IMMEDIATELY PRECEDES 30;
  --> false

->> SUCCEEDS

(v1: T1) ->> (v2: T) -> bool

(v1: T1) SUCCEEDS (v2: T2) -> bool

Returns true if one range/value is completely to the right of another range/value.

Arguments

The arguments must both be ranges, or a range and a corresponding numeric/timepoint value.

Notes

The range r1 is completely on the right of range r2 if it holds that LBOUND(r1) >= RBOUND(r2).

a SUCCEEDS b is equivalent to b PRECEDES a.

Examples

SELECT RANGE(35, 40) ->> RANGE(20, 30);
  --> true

SELECT RANGE(25, 40) SUCCEEDS RANGE(20, 30);
  --> false

SELECT 40 ->> RANGE(20, 30);
  --> true

SELECT RANGE(25, 40) SUCCEEDS 30;
  --> false

|>> IMMEDIATELY SUCCEEDS

(v1: T1) |>> (v2: T) -> bool

(v1: T1) IMMEDIATELY SUCCEEDS (v2: T2) -> bool

Returns true if one range/value is immediately to the right of another range/value.

Arguments

The arguments must both be ranges, or a range and a corresponding numeric/timepoint value.

Notes

The range r1 is immediately on the right of range r2 if it holds that LBOUND(r1) = RBOUND(r2).

a IMMEDIATELY SUCCEEDS b is equivalent to b IMMEDIATELY PRECEDES a.

Examples

SELECT RANGE(30, 40) |>> RANGE(20, 30);
  --> true

SELECT RANGE(35, 40) IMMEDIATELY SUCCEEDS RANGE(20, 30);
  --> false

SELECT 30 |>> RANGE(20, 30);
  --> true

SELECT RANGE(35, 40) IMMEDIATELY SUCCEEDS 30;
  --> false