MENU


Comparison

COALESCE()

COALESCE(expr1, expr2, ...)

Returns the first non-NULL argument, or NULL if every argument is NULL.

SELECT COALESCE(NULL, NULL, 3, 4, 5, NULL);
SELECT COALESCE(NULL, NULL, NULL);

3; NULL


SELECT COALESCE(NULL, NULL, 3, 4, 5, NULL);

SELECT COALESCE(NULL, NULL, NULL);

SELECT COALESCE(NULL, NULL, 3, 4, 5, NULL):
COALESCE(NULL, NULL, 3, 4, 5, NULL)
3
SELECT COALESCE(NULL, NULL, NULL):
COALESCE(NULL, NULL, NULL)
NULL

GREATEST() / LEAST()

GREATEST(value1, value2, ...)
LEAST(value1, value2, ...)

Return the largest, respectively smallest, of their arguments, using the same comparison rules as other operators (so a mix of strings and numbers may be compared numerically).

SELECT GREATEST(1, 3.2, 2);
SELECT LEAST('x','y','z');

3.2; 'x'


SELECT GREATEST(1, 3.2, 2);

SELECT LEAST('x','y','z');

SELECT GREATEST(1, 3.2, 2):
GREATEST(1, 3.2, 2)
3.2
SELECT LEAST('x','y','z'):
LEAST('x','y','z')
x

INTERVAL()

INTERVAL(N, N1, N2, N3, ...)

Returns 0 if N < N1, 1 if N < N2 (but N >= N1), and so on — effectively counting how many of the trailing arguments N is at or above. The N1..Nn arguments are meant to be given in ascending order; MySQL performs a fast binary search when there are more than a handful of arguments, which requires that order to produce a meaningful result.

SELECT INTERVAL(5,1,3,7,8,9);

2 — 5 is at or above both 1 and 3 (the two integers smaller than 5), but below 7.

SELECT INTERVAL(5,5,1,3);

3 — with an out-of-order argument list, INTERVAL() simply advances past every argument N is greater than or equal to (5>=5, 5>=1, 5>=3), so the result should not be relied upon unless the Nn arguments are sorted ascending.


SELECT INTERVAL(5,1,3,7,8,9);

SELECT INTERVAL(5,5,1,3);

SELECT INTERVAL(5,1,3,7,8,9):
INTERVAL(5,1,3,7,8,9)
2
SELECT INTERVAL(5,5,1,3):
INTERVAL(5,5,1,3)
3