NULL is one of the most misunderstood concepts in SQL Server. It causes unexpected query results, breaks comparisons that look perfectly reasonable, and trips up developers at every experience level. Understanding exactly what NULL means, and how SQL Server handles it, is fundamental to writing reliable queries and maintaining clean data.

What Does NULL Mean in SQL Server?

NULL means a value is unknown or absent. It is not zero. It is not an empty string. It is not a blank space. It simply means no value exists for that field.

This distinction matters enormously in practice. A salary of zero is a known value. A salary of NULL means we don't know what the salary is. SQL Server treats these completely differently, and if you confuse them, your queries will return wrong results without throwing any errors.

Consider a table of employees where some staff haven't had a middle name recorded:

SELECT * FROM employees WHERE middle_name IS NULL;

This returns every row where middle_name has no recorded value. NULL doesn't belong to any specific data type, and critically, it doesn't equal anything, including another NULL. Two unknown values are not considered equal in SQL.

How Do You Test for NULL in SQL Server?

Use IS NULL and IS NOT NULL. These are the correct operators for checking whether a column contains a NULL value.

Using IS NULL

SELECT * FROM orders WHERE delivery_date IS NULL;

This returns all orders where no delivery date has been recorded. Straightforward enough.

Using IS NOT NULL

SELECT * FROM orders WHERE delivery_date IS NOT NULL;

This returns only orders that have a delivery date recorded.

Why Does = NULL Never Work?

This is where most people get caught out. Writing WHERE delivery_date = NULL looks logical but it will never return any rows. SQL Server evaluates NULL comparisons using three-valued logic: true, false, or unknown. Any comparison involving NULL produces "unknown," and rows that evaluate to unknown are excluded from results.

So = NULL doesn't return false. It returns unknown, which gets filtered out. The same applies to != NULL, <> NULL, and any other comparison operator. Always use IS NULL or IS NOT NULL. No exceptions.

How Do You Prevent NULL Values Entering a Column?

When a column must always contain a value, apply the NOT NULL constraint at table creation. This forces SQL Server to reject any insert or update that would leave that column empty.

CREATE TABLE customers (
    customer_id   INT          NOT NULL,
    first_name    VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) NOT NULL,
    phone_number  VARCHAR(15)  NULL
);

In this example, customer_id, first_name, and email are mandatory. SQL Server will raise an error if any insert attempts to leave them blank. The phone_number column is explicitly marked NULL, meaning it's optional. Being explicit about which columns allow NULL is good practice. It makes the schema self-documenting and prevents ambiguity.

Applying NOT NULL constraints at the database level is far more reliable than relying on application code to enforce data completeness. Application logic changes. Constraints don't, unless a DBA deliberately alters them.

How Do You Replace NULL Values in Query Results?

Sometimes you need to substitute a NULL with a displayable value, particularly when producing reports or feeding data to an application. SQL Server provides ISNULL() for exactly this purpose.

ISNULL() Syntax

ISNULL(expression, replacement_value)

If expression is NULL, the function returns replacement_value. If expression has a value, it returns that value unchanged.

Practical Example

SELECT
    first_name,
    ISNULL(middle_name, 'N/A') AS middle_name
FROM employees;

Where middle_name is NULL, the result set shows 'N/A' instead. This keeps reports readable and prevents downstream applications from having to handle NULL values themselves.

One important detail: the return type of ISNULL() matches the data type of the first expression. If your replacement value is a longer string than the column allows, it may get truncated silently. Always verify that your replacement value fits within the data type constraints of the first expression.

For cross-platform compatibility or more complex substitution logic, COALESCE() is worth knowing. It accepts multiple arguments and returns the first non-NULL value in the list:

SELECT COALESCE(middle_name, preferred_name, 'N/A') AS display_name
FROM employees;

COALESCE() is ANSI standard SQL, whereas ISNULL() is SQL Server-specific.

What Are the Most Common NULL Mistakes in SQL Server?

After two decades of supporting SQL Server environments, these are the mistakes we see most often.

1. Using = NULL in WHERE Clauses

Already covered above, but worth repeating because it's so common. WHERE column = NULL will silently return zero rows. Use IS NULL.

2. Unexpected Results in Aggregate Functions

Most aggregate functions, including SUM(), AVG(), MIN(), and MAX(), ignore NULL values entirely. COUNT(column_name) also ignores NULLs, while COUNT(*) counts all rows regardless. This can produce misleading averages if you're not aware that NULLs are being excluded from the calculation.

-- This average excludes any rows where salary is NULL
SELECT AVG(salary) FROM employees;

If 20 out of 100 employees have NULL salaries, the average is calculated across 80 employees, not 100. Whether that's the right behaviour depends on your use case, but you need to be aware it's happening.

3. NULL in JOIN Conditions

NULL values in join columns cause rows to drop out of results silently. Two NULL values in a join column are not considered equal, so they won't match. If you're getting fewer rows than expected from a join, check whether NULL values in the join columns are the cause.

4. NOT IN with NULLs

This one catches experienced developers off guard. If a subquery used with NOT IN returns any NULL values, the entire NOT IN check returns no rows. This is because SQL Server can't confirm that the value is "not in" a list that contains an unknown element.

-- If the subquery returns any NULLs, this query returns zero rows
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blacklisted_customers);

The safer approach is to use NOT EXISTS, which handles NULLs correctly:

SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklisted_customers bc
    WHERE bc.customer_id = o.customer_id
);

5. Sorting Behaviour

In SQL Server, NULL values sort before other values in ascending order and after other values in descending order. If your application expects NULLs to appear at the end of a result set regardless of sort direction, you'll need to handle this explicitly using CASE expressions in your ORDER BY clause.

Key Takeaways

  • NULL means unknown or absent, not zero or empty. Any comparison using =, !=, or <> against NULL will never return results. Always use IS NULL or IS NOT NULL.
  • Three-valued logic (true, false, unknown) governs how SQL Server evaluates NULL comparisons. Understanding this prevents a large class of subtle query bugs.
  • Apply NOT NULL constraints at the database level to enforce data completeness. Don't rely solely on application code.
  • ISNULL() substitutes a default value for NULLs in query results. COALESCE() is the ANSI-standard alternative and handles multiple fallback values.
  • Watch for NULL-related traps in aggregates, NOT IN subqueries, joins, and sort order. These are the areas where NULL behaviour causes the most operational problems.

NULL handling is one of those topics where a solid theoretical understanding directly translates to fewer production incidents and more trustworthy reporting. If your team is dealing with unexpected query results, data quality issues, or schema design questions in SQL Server, DBA Services has the operational experience to help you work through them properly.