Null Functions In MySQL

In MySQL, NULL functions are special functions used to handle NULL Values, indicating that a data value does not exist in the Table column.

NULL functions in SQL such as "ISNULL()", "NULLIF()", and "COALESCE()".

Use Cases of NULL Functions In MySQL:

Default Values: NULL Function allows us to replace NULL values with a default value within Columns.
Data Cleaning: It ensures that NULL values are handled appropriately during data analysis and reporting.
Comparison: It helps us to compare columns and return NULL or another value based on the comparison.
Aggregation: It ensures aggregate functions return meaningful results even when NULL values exist.

Commonly used NULL Functions:

Products Table:

product_code product_name price order_units discount
1 Ipad 100 56 10
2 Tablet 42

product_code	product_name	price	order_units	discount
1	Ipad	100	56	10
2	Tablet	42

IS NULL / IS NOT NULL:

These are not functions but logical operators to check if a value is NULL or not.

Example:

SELECT columnName
FROM tableName
WHERE columnName IS NULL;

ISNULL() Function In MySQL:

Returns expr2 if expr1 is NULL, otherwise returns expr1.

Syntax:

ISNULL(expr1, expr2)

Syntax Example:

SELECT ISNULL(columnName, 'Default') AS result
FROM tableName;

In the below example, it will fetch all products where the `order_units` column is an empty string or NULL value and replace it with `0` while retrieving from the Table.

Example:

SELECT 
    product_code, 
    product_name, 
    ISNULL(order_units, 0) AS order_units
FROM products;

COALESCE(expr1, expr2, ...):

Returns the first non-NULL expression in the list.

Syntax:

COALESCE(expr1, expr2, exprN)

Syntax Example:

SELECT COALESCE(column1, column2, 'Not Available') AS result
FROM tableName;

Example:

SELECT 
COALESCE(product_name, price) AS product_info
FROM products;

NULLIF() Function In MySQL:

Returns NULL if expr1 equals expr2, otherwise returns expr1.

Syntax:

NULLIF(expr1, expr2)

Syntax Example:

SELECT NULLIF(column1, 0) AS result
FROM tableName;

In the below example, it will fetch all `products` where the `discount` column is an empty string.

Example:

SELECT 
    product_code, 
    product_name, 
    NULLIF(discount, '') AS product_coupon
FROM products;

Combining ISNULL() and Aggregation:

In the below query, We have a table "products" and we want to calculate the total price of all products whose `price` is greater than `50` considering NULL `discount` as 0.

Example:

SELECT SUM(price - ISNULL(discount, 0)) AS actual_price
FROM products
WHERE price > 50

<-- Case

Operators -->