Switch to: V9V8V7V6V5

Comparison Functions

Comparison operators return 1 (TRUE), 0 (FALSE) or NULL values. These functions work with both numbers and strings. Strings, if required, are converted automatically to numbers, and vice versa.

Comparison operators of SQL work according to the following rules:

  • If one or both arguments is NULL then the result is also NULL.
  • If both arguments are strings then they are compared as strings.
  • If both arguments are integer numbers then they are compared as integers.
  • In all other cases arguments are compared as real numbers.

Operator '='

Operator Equal.

SELECT IF( 1 = 0, 1, 0 );
=> 0
SELECT IF( '0' = 0, 1, 0 );
=> 1
SELECT IF( '0.0' = 0, 1, 0 );
=> 1
SELECT IF( '0.01' = 0, 1, 0 );
=> 0
SELECT IF( '.01' = 0.01, 1, 0 );
=> 1

Operator '<>' / '!='

Operator Not Equal.

2 forms are supported: <> - SQL Standard != - C syntax

SELECT IF( '.01' <> '0.01', 1, 0 );
=> 1
SELECT IF( .01 <> '0.01', 1, 0 );
=> 0
SELECT IF( 'zapp' <> 'zappp', 1, 0 );
=> 1

Operator '<='

Operator Less Than Or Equal.

SELECT IF( 0.1 <= 2, 1, 0 );
=> 1

Operator '<'

Operator Less Than.

SELECT IF( 2 < 2, 1, 0 );
=> 0

Operator '>='

Operator Greater Than Or Equal.

SELECT IF( 2 >= 2, 1, 0 );
=> 1

Operator '>'

Operator Greater Than.

SELECT IF( 2 > 2, 1, 0 );
=> 0

Operator BETWEEN

expr BETWEEN min AND max

This operator is equivalent to the expression (min ⇐ expr AND expr ⇐ max) that has all arguments of the same type. Otherwise type conversions will be performed as described above.

SELECT IF( 1 BETWEEN 2 AND 3, 1, 0 );
=> 0
SELECT IF( 'b' BETWEEN 'a' AND 'c', 1, 0 );
=> 1
SELECT IF( 2 BETWEEN 2 AND '3', 1, 0 );
=> 1


expr NOT BETWEEN min AND max

Equivalent to NOT (expr BETWEEN min AND max).

Operator IN

expr IN (value,…)

Returns 1 if the expression EXPR is equal to any value from the list IN, otherwise returns 0.

Note: if all values are constants, then they are sorted according to type. The search in this case is done with use of a Binary search, i.e. very fast.

SELECT IF( 2 IN (0,3,5,'wefwf'), 1, 0 );
=> 0
SELECT IF( 'wefwf' IN (0,3,5,'wefwf'), 1, 0 );
=> 1

expr NOT IN (value,…)

Equivalent to NOT (expr IN (value,…)).

Operator IS NULL

IS [NOT] NULL

Tests if a value is NULL.

SELECT IF(1 IS NULL, 1, 0), IF(0 IS NULL, 1, 0 ), IF(NULL IS NULL, 1, 0 );
=> 0 0 1
SELECT IF(1 IS NOT NULL, 1, 0), IF(0 IS NOT NULL, 1, 0 ), IF(NULL IS NOT NULL, 1, 0);
=> 1 1 0

Function ISNULL()

ISNULL(expression_value, replacement_value)

Returns replacement value if expression value is NULL, otherwise returns expression value.

SELECT ISNULL(2+2, 1);
=> 4
SELECT ISNULL(1/0, 1);
=> 1

Note: comparison of NULL values by operator = always will return FALSE.

SELECT IF( NULL = NULL, 1, 0 );
=> 0

Row constructor comparison operators

Row constructor is a sort of expression which produces not a single value but row of values.

Common rules for comparison of such values:

  • Rows must have the same number of items;
  • Corresponded items are evaluated (left to right) and then compared;
  • Row constructor comparisons are allowed when the operator is =, <>, <, ⇐, > or >=.

==, <> specific rules:

  • Two rows are considered equal if all their corresponding members are non-null and equal.
  • The rows are unequal if any corresponding members are non-null and unequal.
  • Otherwise, the result of the row comparison is unknown (null).

Examples:

SELECT IF( (1,1) = (1,1), 1, 0 );
=> 1
SELECT IF( (1,0) = (1,1), 1, 0 );
=> 0
SELECT IF( (1,1.1) = (1,1/0), 1, 0 );
=> 0, because of 1/0 == NULL
SELECT IF( (1,1.1) <> (1,1/0), 1, 0 );
=> 0, because of 1/0 == NULL

<, ⇐, >, >= specific rules:

  • The row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found;
  • If either of this pair of elements is null, the result of the row comparison is unknown (null).
  • Otherwise, comparison of this pair of elements determines the result.

In other words:

SELECT IF( (a,b) < (c,d), 1, 0);
-- is equivalent to 
SELECT IF( a < c OR (a = c AND b < d), 1, 0);

Examples:

SELECT IF( (1,1) > (1,1), 1, 0 );
=> 0
SELECT IF( (1,2) > (1,1), 1, 0 );
=> 1
SELECT IF( (1,1.1) > (1,1/0), 1, 0 );
=> 0, because of 1/0 == NULL