Table of Contents

In SQL Server, NULL is a special marker used to indicate that a data value does not exist, is unknown, or is currently unassigned. It is a frequent source of bugs, performance quirks, and logical errors for developers coming from traditional programming languages because it does not behave like a standard value (such as an empty string '' or the number 0).

1. The Core Misconception: Three-Valued Logic

In standard programming, expressions evaluate to either TRUE or FALSE. In SQL Server, because NULL represents an unknown state, evaluation follows Three-Valued Logic, introducing a third state: UNKNOWN.

When you attempt to compare any value directly to NULL using standard comparison operators (=, <>, >, <, etc.), the result is never TRUE or FALSE—it always evaluates to UNKNOWN.

  • The Problem:

    SQL

    -- This will NEVER return rows, even if some rows have a NULL Email
    SELECT * FROM dbo.Employees WHERE Email = NULL;
    
  • The Solution: You must explicitly use the IS NULL or IS NOT NULL operators.

    SQL

    SELECT * FROM dbo.Employees WHERE Email IS NULL;
    

2. Functions for Managing and Transforming NULLs

SQL Server provides several built-in functions designed specifically to handle, evaluate, or substitute NULL values during query processing.

A. ISNULL()

Replaces NULL with a specified replacement value.

  • Syntax: ISNULL(check_expression, replacement_value)

  • Note: The data type of the replacement value is implicitly converted to the data type of the check expression.

SQL

SELECT EmployeeID, ISNULL(MiddleName, 'N/A') AS MiddleName
FROM dbo.Employees;

B. COALESCE()

Evaluates the arguments in order and returns the first non-null expression found in the list.

  • Advantages over ISNULL(): COALESCE is ANSI-SQL compliant, can accept multiple expressions (more than two), and determines the resulting data type using data type precedence rules.

SQL

-- Returns the first available contact method: Mobile, WorkPhone, or 'None'
SELECT COALESCE(MobilePhone, WorkPhone, 'None') AS PrimaryContact
FROM dbo.Contacts;

C. NULLIF()

Compares two expressions. If the expressions are equal, NULLIF returns NULL. If they are not equal, it returns the first expression. This is frequently used to prevent division-by-zero errors.

SQL

-- If ActualSales is 0, return NULL instead of causing a division-by-zero error
SELECT TargetSales / NULLIF(ActualSales, 0) AS PerformanceRatio
FROM dbo.Metrics;

3. Mathematical Operations and Concatenation

Any arithmetic calculation or string concatenation involving a NULL value automatically results in NULL.

  • Arithmetic: SELECT 100 + NULL; returns NULL.

  • String Concatenation: If you concatenate columns to form a full name, a single NULL in any middle or last name column will collapse the entire concatenated string into NULL unless handled.

Safe Concatenation Example:

SQL

SELECT 
    ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') AS FullName
FROM dbo.Employees;

5. NULL in Aggregations and Grouping

Aggregate functions handle NULL values in specific ways that can catch developers off guard:

  • Exclusion: Functions like SUM(), AVG(), MAX(), MIN(), and COUNT(ColumnName) completely ignore NULL values.

  • The Exception (COUNT(*)): Unlike column-specific counts, COUNT(*) counts total rows regardless of whether columns contain NULLs.

SQL

-- If a table has 10 rows, but Salary is NULL for 2 of them:
SELECT 
    COUNT(*) AS TotalRows,       -- Returns 10
    COUNT(Salary) AS ValidSalaries -- Returns 8
FROM dbo.Employees;

Grouping Behavior

When using GROUP BY, SQL Server treats all NULL values as equal to each other. Therefore, multiple NULL entries in a grouped column will be collapsed into a single group.

Categorized in:

SQL Server, SQL Basic,