How to Use MySQL Comparison Functions - Coalesce, Greatest, Interval, Isnull, Least

Using Comparison Functions

Comparison Functions accept one or more arguments and return a value by comparing the arguments. Note that Comparison Functions are different to Comparison Operators. Refer to Using Comparison Operators, Part I and Using Comparison Operators, Part II.

Here are comparison functions in MySQL.

Function	Description
Coalesce	COALESCE(value1, value2, value3, ...) Returns the first non-NULL value in the comma delimited list, or NULL if there are no non-NULL values. See Practice #1.
Greatest	GREATEST(value1, value2, value3, ...) With a list of comma separated values in the argument, GREATEST() returns the largest (maximum-valued) argument. See Practice #2. The arguments are compared using the following rules: If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers. If the return value is used in a REAL/FLOAT context or all arguments are real-valued, they are compared as reals. If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings. In all other cases, the arguments are compared as case-insensitive strings. GREATEST() returns NULL if any argument is NULL.
Interval	INTERVAL(N, N1, N2, N3, ...) Returns the first occurrence of the index position number of the argument that is greater than the first argument. Index position number for N1 is 0, N2 is 1, N3 is 2, and so on. Only the very first index position number is returned. Any subsequent comparisons that meet the criteria are ignored. To be more specific: INTERVAL returns 0 if N < N1. INTERVAL returns 1 if N < N2. INTERVAL returns 2 if N < N3. INTERVAL returns 3 if N < N4. ... It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast). INTERVAL() returns -1 is the first argument N is NULL. INTERVAL() returns the last index position number plus one if N is not less than any argument in the list. See Practice #3.
ISNULL	ISNULL(expr) If expr is NULL, ISNULL() returns 1, otherwise it returns 0. See Practice #4.
Least	LEAST(value1, value2, value3, ...) With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the same rules as for GREATEST(). See Practice #5.

Practice #1: Using COALESCE function.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns the first non-NULL value in
the comma separated list. It returns NULL if all values
are NULL in the list. 
*/
SELECT COALESCE(NULL, 2, 4, NULL, 5) AS col1, 
       COALESCE(NULL, NULL, NULL) AS col2;

Query result set - 1 row returned:
Using COALESCE function.

Practice #2: Using GREATEST function.

/*
This query finds the greatest value in this list based
on what the context the list is in.
*/
SELECT GREATEST(2, 5, 0) AS integer_context,
       GREATEST(34.0, 3.0, 5.0, 767.0) AS real_context,
       GREATEST('B', 'a', 'c', 'd') AS case_insensitive_context;

Query result set - 1 row returned:
Using GREATEST function.

Practice #3: Using INTERVAL function.

/*
This query finds the first occurrence of the index position 
number for the argument that is greater than the first argument.
 
In the first column, 23 is less than 30 which has a
index position number of 3 so it returns 3.
 
In the second column, 10 is less than 100 which has a 
index position number of 2 so it returns 2.
 
In the third column, 22 is less than 23 which has a
index position number of 0 so it returns 0.
 
In the fourth column, -1 is returned because the first
argument is NULL.
 
In the fifth column, 7 is returned because 501 is not 
less than any number in the list. So it returns is the 
last index number in the list plus one which is 6+1.
*/
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200) AS col1,
       INTERVAL(8, 1, 8, 100, 1000) AS col2,
       INTERVAL(22, 23, 30, 44, 200) AS col3,
       INTERVAL(NULL, 23, 30, 44, 200) AS col4,
       INTERVAL(501, 1, 15, 17, 30, 44, 200, 449) AS col5;

Query result set - 1 row returned:
Using INTERVAL function.

Practice #4: Using ISNULL function.

/*
This query retrieves the employee who does not 
report to anyone.
*/
SELECT EmployeeID, FirstName, LastName
FROM employees 
WHERE ISNULL(ReportsTo)=1;
 
-- This query returns the same result.
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ISNULL(ReportsTo)=TRUE;

Query result set - 1 row returned:
Using ISNULL function.

Practice #5: Using LEAST function.

/*
This query finds the returns the smallest (minimum-valued) 
value in this list based on what the context the list is in.
*/
SELECT LEAST(2, 5, 0) AS integer_context,
       LEAST(34.0, 3.0, 5.0, 767.0) AS real_context,
       LEAST('B', 'a', 'c', 'd') AS case_insensitive_context;

Query result set - 1 row returned:
Using LEAST function.

Next tutorial: Using Numeric Functions
Back to Section Index: Single Row Functions
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search