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.

SELECT IF( 1 > 2, 1, 0 );
=> 0
SELECT IF( 2 > 1, 1, 0 );
=> 1
SELECT IF( 'aa' > 'aa', 1, 0 );
=> 0
SELECT IF( 'ab' > 'aa', 1, 0 );
=> 1

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 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 Less Than Or Equal.

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

Operator Less Than.

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

Operator Greater Than Or Equal.

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

Operator Greater Than.

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

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).

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,…)).

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

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