How does SQL Server handle NULL values?

How SQL Server Handles NULL Values

NULL represents:

• A missing value

• An unknown value

• A value that is not available

It is not the same as:

• Zero (0)

• An empty string ('')

• A space (' ')

• The text 'NULL'

1. Comparing NULL values

Do not use = or <> to check for NULL.

-- Incorrect
WHERE Email = NULL

Use IS NULL:

WHERE Email IS NULL

To find non-NULL values:

WHERE Email IS NOT NULL

Because NULL means unknown, even the following comparison is not TRUE:

NULL = NULL

It produces an unknown result.

2. NULL in calculations

Most calculations involving NULL return NULL.

SELECT 100 + NULL;  -- NULL
SELECT 100 * NULL;  -- NULL

Use ISNULL() or COALESCE() to provide a replacement value:

SELECT Salary + ISNULL(Bonus, 0) AS TotalSalary
FROM Employees;

SELECT Salary + COALESCE(Bonus, 0) AS TotalSalary
FROM Employees;

3. ISNULL vs COALESCE

ISNULL(Bonus, 0)

• SQL Server-specific

• Accepts two arguments

COALESCE(Bonus, Allowance, 0)

• Standard SQL

• Accepts multiple arguments

• Returns the first non-NULL value

4. NULL in aggregate functions

Most aggregate functions ignore NULL values.

SELECT AVG(Salary), SUM(Salary), MIN(Salary), MAX(Salary)
FROM Employees;

These functions ignore rows where Salary is NULL.

COUNT behaves differently:

COUNT(*)         -- Counts every row
COUNT(Salary)    -- Counts only non-NULL Salary values

5. NULL in conditions

SQL Server uses three-valued logic:

• TRUE

• FALSE

• UNKNOWN

Consider:

WHERE Salary > 50000

Rows where Salary is NULL produce UNKNOWN, so they are not returned.

To include them:

WHERE Salary > 50000
   OR Salary IS NULL;

6. NULL in string concatenation

The recommended method is CONCAT, which treats NULL as an empty string:

SELECT CONCAT(FirstName, ' ', MiddleName, ' ', LastName)
FROM Employees;

With the + operator, a NULL value normally makes the entire result NULL:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName
FROM Employees;

A safer version is:

SELECT FirstName + ' ' +
       ISNULL(MiddleName + ' ', '') +
       LastName
FROM Employees;

7. Defining nullable columns

A column that allows NULL:

CREATE TABLE Employees
(
    EmployeeId INT NOT NULL,
    MiddleName VARCHAR(50) NULL
);

• NULL — missing values are allowed.

• NOT NULL — a value is required.

Key points

• NULL means unknown or missing.

• Use IS NULL and IS NOT NULL.

• Most calculations with NULL return NULL.

• Most aggregate functions ignore NULL.

• COUNT(*) counts every row; COUNT(column) ignores NULL.

• Use ISNULL() or COALESCE() to replace NULL.

• Use CONCAT() for safer string concatenation.