Answer.

In SQL, the value NULL signifies "unknown" or "missing" data. It behaves in a special way:

• Comparing NULL with any value using = or <> always results in UNKNOWN, rather than TRUE or FALSE.
• To check for NULL, one needs to use IS NULL or IS NOT NULL.
• Any arithmetic or logical operation involving NULL will yield NULL.

Example:

SELECT * FROM users WHERE name = NULL;       -- will return no rows
SELECT * FROM users WHERE name IS NULL;       -- correct
SELECT 1 + NULL;  -- Result: NULL

To replace NULL, functions like COALESCE(foo, 0) (which takes the first non-NULL value) or ISNULL(foo, 'default') in SQL Server are used.

Trick Question.

Question: What will the condition WHERE some_column <> 'value' return in rows where some_column is NULL?

Answer:

In these rows, the condition will not be satisfied: the comparison with NULL will result in UNKNOWN, not TRUE. Therefore, rows with NULL will not be included in the selection.

Example:

-- some_column | ...
-- NULL        | ...
-- 'abc'       | ...
-- 'value'     | ...
SELECT * FROM table WHERE some_column <> 'value';
-- The row with NULL will not be included

---

Story

In a bank report, the condition WHERE status <> 'closed' mistakenly did not account for accounts with status = NULL (for example, new applications). As a result, the count of active accounts was incorrectly calculated — it turned out to be 15% lower.

Story

In an email personalization service, COALESCE(user_name, 'Dear Client') was forgotten in the mailing list. Nearly a thousand clients received greetings without any name at all, which was perceived as an automation error.

Story

During the database migration, LEFT JOIN returned more rows than expected. It turned out that the left table had fields with NULL; filters like WHERE b.field = 'X' after the JOIN discarded those rows, resulting in some data loss.