COALESCE and ISNULL Functions

In addition to using NULL, SQL Server provides two functions—COALESCE and ISNULL—to handle situations where NULL values need to be managed or replaced with default values.

1. COALESCE Function

The COALESCE function allows you to return the first non-NULL expression in a list. It comes in handy when you want to provide a fallback value in case a particular column is NULL.

Query:

--**COALESCE FUNCTION**
SELECT COALESCE(PhoneNumber, 'No Phone') AS ModifiedPhoneNumber
FROM Employees;

Output:

Explanation: The Output shows that first row with the NULL value got replaced or changed by “No Phone” Value.

2. ISNULL Function

The ISNULL function is similar to COALESCE and replaces NULL with a specified replacement value in the whole table.

-- Using ISNULL
SELECT ISNULL(PhoneNumber, 'No Phone') AS ModifiedPhoneNumber
FROM Employees;

Output:

Explanation: Every value in the table with NULL Value got replaced by the “No Phone” Value.

In SQL Server, NULL represents the absence of a value in a column. It signifies missing or undefined data, distinct from zero or an empty string. SQL Server uses a three-valued logic, and handling NULL is crucial for accurate querying and reporting. Conditions like IS NULL and IS NOT NULL are used to check for NULL values, and functions like COALESCE assist in managing and substituting NULL in expressions.

Prerequisite:

Before starting to learn the concept of the NULL one only needs proper concentration at first and a very basic knowledge of database commands like Insert, and Create table, and then you are set to go on this journey.