Not equal `<>` != operator on NULL

sql

null-handling

sql-queries

best-practices

byNikita Barsukov·Jan 6, 2025

In SQL, a NULL isn't a value but a placeholder signifying an absence of value. Comparisons using inequality operators <> or != always return false because unknowns cannot be equated. For effective NULL checks, use the IS NULL or IS NOT NULL constructs:

-- Grabbing all data in the absence of the darkness(NULL)
SELECT *
FROM your_table
WHERE your_column IS NOT NULL;

This verifies your check for data presence or absence, as NULL precisely can't be compared using standard inequality operators.

Advanced NULL handling in SQL

Understanding the NULL

Remember, NULL is the SQL way of saying I don’t know. It's neither zero, an empty string, nor even another NULL. This is why inequality operators don't work like you'd expect with NULL. You're essentially trying to compare apples and mystery fruits.

Handling NULL Like a Pro

In SQL Server, the ANSI_NULLS setting can change how != and <> behave with NULLs. But, heads up! Microsoft is planning to pull the plug on this. So, it's probably a "NULL and void" (😉) practice now.

Filling the NULL Gaps

When you want to handle NULLs and empty strings in one sweeping motion, whip out the ISNULL function. This substitutes a NULL with a specified value, making your SQL life easier:

-- Treating NULLs as chameleons (replace them with '')
SELECT *
FROM your_table
WHERE ISNULL(your_column, '') != 'some_value';

Precision in NULL Comparisons

For more rigorous comparing dig into IS DISTINCT FROM. It treats two NULL as the same and everything else as it should be:

-- The query equivalent of comparing apples to actual apples (and not mysterious fruits)
SELECT *
FROM your_table
WHERE your_column IS DISTINCT FROM 'some_value';

The above query includes the rows where your_column is either NULL or doesn't contain 'some_value', without failing due to the NULLs.

Working with NULL your way

NULL vs Empty Strings

Differentiating 'no data' (NULL) from 'empty data' (empty strings) will clear up your data like a sunny day following foggy weather. Remember, NULL isn't the same as an empty string.

When NULL strolls into Aggregate functions

Watch out for NULLs when they sneak into aggregate functions. They're often ignored in fun functions like SUM() or COUNT(). For inclusive results, you'll need to convert NULLs using ISNULL or its equivalent.

Set operations: NULL’s Playground

Set operations like JOIN or WHERE EXISTS, NULLs can cause some unexpected hiccups. Don't forget to explicitly include logic to handle NULLs, or they'll drop in like uninvited guests.

explain-codes / Sql / Not equal <> != operator on NULL

Linked

Sql is null and = null



Mysql comparison with null value



What is this operator <=> in MySQL?



Why does NULL = NULL evaluate to false in SQL server



How to use NULL or empty string in SQL

