|
Using MySQL 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.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT COALESCE(NULL, 2, 4, NULL, 5) AS col1, COALESCE(NULL, NULL, NULL) AS col2;
Query result set - 1 row returned:
Practice #2: Using GREATEST function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
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:
Practice #3: Using INTERVAL function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
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:
Practice #4: Using ISNULL function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EmployeeID, FirstName, LastName FROM employees WHERE ISNULL(ReportsTo)=1;
SELECT EmployeeID, FirstName, LastName FROM employees WHERE ISNULL(ReportsTo)=TRUE;
Query result set - 1 row returned:
Practice #5: Using LEAST function.
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
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:
Other tutorials in this category 1. Using String Functions, Part 1
2. Using String Functions, Part 2
3. Using String Functions, Part 3
4. Using MySQL Date and Time Functions, Part 1
5. Using MySQL Date and Time Functions, Part 2
6. Using MySQL Date and Time Functions, Part 3
7. Using MySQL Control Flow Functions
8. Using MySQL Conversion Functions
9. Using Aggregate Functions in MySQL
10. MySQL RANK() function
11. MySQL DENSE_RANK() function
12. MySQL ROW_NUMBER() function
13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function
14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL
15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL
16. Use LEAST function to find the SMALLEST value from multiple arguments
17. How to make case-sensitive comparison in MySQL
18. Use GREATEST function to find the LARGEST value from multiple arguments
19. MySQL MID() function with examples
20. MySQL LOCATE() function with examples
21. MySQL SUBSTR() function with examples
22. MySQL POSITION() function with examples
23. How to use MySQL REPLACE function in SELECT and UPDATE queries
Back to Tutorial Index Page
|