MySQL provides several comparison functions that are useful for handling null values and comparing multiple expressions. These functions include COALESCE, GREATEST, LEAST, IS NOT NULL, and IS NULL.
Comparison functions in MySQL compare two or more values and return a Boolean value (TRUE or FALSE) or a specific value based on the comparison. These functions are commonly used in SELECT statements, UPDATE statements, and WHERE clauses to filter data or manipulate values.
COALESCE
The COALESCE function returns the first non-NULL value from a list of arguments. It’s commonly used to handle NULL values, providing a default value when a specific field or expression is empty. The function takes a comma-separated list of expressions as arguments. If all arguments are NULL, COALESCE() also returns NULL.
SELECT COALESCE(null, 5, 10); -- Output: 5
In this example, COALESCE returns 5 since it is the first non-null expression.
GREATEST
The GREATEST function returns the largest value from a list of arguments. It’s used to identify the maximum value among multiple numerical or non-numerical expressions. The function takes two or more arguments and compares them based on their data types. If any argument is NULL, GREATEST() returns NULL.
SELECT GREATEST(3, 8, 6); -- Output: 8
Here, GREATEST returns 8 as it is the largest value among the provided expressions.
LEAST
The LEAST function returns the smallest value from a list of arguments. It’s used to determine the minimum value among multiple numerical or non-numerical expressions. The function takes two or more arguments and compares them based on their data types. If any argument is NULL, LEAST() returns NULL.
SELECT LEAST(15, 9, 12); -- Output: 9
In this case, LEAST returns 9 as it is the smallest value among the given expressions.
IS NOT NULL
The IS NOT NULL predicate checks if a value or expression is not NULL. It returns TRUE if the value is not NULL and FALSE if it is NULL. This function is useful for evaluating conditions and filtering data based on the presence or absence of NULL values.
SELECT * FROM employees WHERE salary IS NOT NULL;
This query retrieves all rows from the “employees” table where the “salary” column is not null.
IS NULL
The IS NULL predicate checks if a value or expression is NULL. It returns TRUE if the value is NULL and FALSE if it is not NULL. This function is counterpart to IS NOT NULL and serves the same purpose of evaluating conditions and filtering data based on NULL values.
SELECT * FROM customers WHERE email IS NULL;
This query fetches rows from the “customers” table where the “email” column is null.
These comparison functions and conditions provide flexibility when working with null values and comparing multiple expressions, allowing for effective data retrieval and manipulation in MySQL.