SQL Server NULL Functions
NULL Functions in SQL Server are used to handle missing, unknown or NULL values in database columns.
What is NULL?
In SQL Server, NULL represents a missing, unknown or unavailable value.
NULL is not the same as zero, an empty string or a blank space. It means that no value is available.
Suppose an employee has not provided a phone number. The PhoneNumber column may contain NULL because the value is not available.
Common NULL Functions
| Function | Purpose | Example |
|---|---|---|
| ISNULL() | Replaces a NULL value with a specified value. |
ISNULL(Phone, 'Not Available')
|
| COALESCE() | Returns the first non-NULL value from a list of expressions. |
COALESCE(Phone, Email, 'N/A')
|
| NULLIF() | Returns NULL when two expressions are equal. |
NULLIF(10, 10)
|
1. ISNULL() Function
The ISNULL() function is used to replace a NULL value with a specified replacement value.
Syntax
ISNULL(expression, replacement_value)
Simple Example
SELECT ISNULL(NULL, 'Not Available');
Not Available
Example with Column
SELECT
EmployeeName,
ISNULL(PhoneNumber, 'Not Available') AS PhoneNumber
FROM Employees;
If PhoneNumber contains NULL, SQL Server displays Not Available.
2. COALESCE() Function
The COALESCE() function returns the first non-NULL value from a list of expressions.
Syntax
COALESCE(expression1, expression2, expression3, ...)
Example
SELECT
COALESCE(NULL, NULL, 'Hello');
Hello
SQL Server checks the expressions from left to right and returns the first non-NULL value.
Practical Example
SELECT
EmployeeName,
COALESCE(PhoneNumber, Email, 'No Contact Available')
AS ContactInformation
FROM Employees;
If PhoneNumber is NULL, SQL Server checks Email. If Email is also NULL, it returns No Contact Available.
3. NULLIF() Function
The NULLIF() function returns NULL when two expressions are equal.
If the two expressions are different, the first expression is returned.
Syntax
NULLIF(expression1, expression2)
Example 1: Values are Equal
SELECT NULLIF(10, 10);
NULL
Example 2: Values are Different
SELECT NULLIF(10, 20);
10
How to Check for NULL Values?
We should use IS NULL and IS NOT NULL to check NULL values.
IS NULL
SELECT *
FROM Employees
WHERE PhoneNumber IS NULL;
This query returns employees whose PhoneNumber is NULL.
IS NOT NULL
SELECT *
FROM Employees
WHERE PhoneNumber IS NOT NULL;
This query returns employees whose PhoneNumber contains a value.
NULL vs Zero vs Empty String
| Value | Meaning |
|---|---|
| NULL | Value is missing or unknown. |
| 0 | Numeric value zero. |
| '' | Empty string. |
| ' ' | String containing a space. |
Practical Example with Employees
Create Table
CREATE TABLE Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
PhoneNumber VARCHAR(20),
Email VARCHAR(100)
);
Insert Data
INSERT INTO Employees
VALUES
(1, 'Rahul', '9876543210', 'rahul@gmail.com'),
(2, 'Priya', NULL, 'priya@gmail.com'),
(3, 'Amit', NULL, NULL);
Display Replacement for NULL
SELECT
EmployeeName,
ISNULL(PhoneNumber, 'Phone Not Available') AS PhoneNumber
FROM Employees;
Use COALESCE()
SELECT
EmployeeName,
COALESCE(
PhoneNumber,
Email,
'No Contact Available'
) AS Contact
FROM Employees;
Find Employees Without Phone Number
SELECT *
FROM Employees
WHERE PhoneNumber IS NULL;
ISNULL() vs COALESCE()
| ISNULL() | COALESCE() |
|---|---|
| Accepts two arguments. | Can accept multiple expressions. |
| SQL Server-specific function. | Standard SQL expression. |
ISNULL(Phone, 'N/A')
|
COALESCE(Phone, Email, 'N/A')
|
When Do We Use NULL Functions?
- To handle missing database values.
- To replace NULL values with meaningful values.
- To display user-friendly reports.
- To select the first available value from multiple columns.
- To check whether a column contains NULL.
- To safely handle missing data during calculations.
Important Points
- NULL means the value is missing or unknown.
- NULL is different from zero.
- NULL is also different from an empty string.
- Use IS NULL to find NULL values.
- Use IS NOT NULL to find values that are not NULL.
- ISNULL() can replace a NULL value.
- COALESCE() returns the first non-NULL value.
- NULLIF() returns NULL when two expressions are equal.
Summary
NULL handling is an important part of SQL Server development. In this topic, we learned how NULL values work and how to use ISNULL(), COALESCE(), NULLIF(), IS NULL and IS NOT NULL. These techniques are useful when working with incomplete or missing database information.