NULL Functions

Handling NULL with Dedicated Functions

Chapter 12 introduced COALESCE as a general NULL-handling tool. This topic rounds out the picture with COALESCE's dialect-specific two-argument cousins, and flags one genuinely confusing naming collision.

COALESCE — Quick Recap

sql
SELECT COALESCE(amount, 0) AS amount_or_zero FROM orders;

COALESCE(a, b, c, ...) returns the first non-NULL argument in its list — it can take any number of arguments and is standard SQL, portable across every major dialect. For Bob's NULL amount, this returns 0; for every other row, it returns the actual amount.

IFNULL() — MySQL/SQLite's Two-Argument Shortcut

sql
-- MySQL / SQLite
SELECT IFNULL(amount, 0) AS amount_or_zero FROM orders;

IFNULL(expr, replacement) takes exactly two arguments and returns expr if it's non-NULL, otherwise replacement. It behaves identically to COALESCE(expr, replacement) when there are only two arguments — it's simply a shorter, dialect-specific spelling for the common two-argument case.

NVL() — Oracle's Two-Argument Shortcut

sql
-- Oracle
SELECT NVL(amount, 0) AS amount_or_zero FROM orders;

NVL(expr, replacement) is Oracle's equivalent of IFNULL() — same two-argument behavior, different name. Oracle also supports NVL2(expr, value_if_not_null, value_if_null), a three-argument variant with no direct MySQL/SQLite equivalent.

The ISNULL() Naming Collision — A Genuine Gotcha

SQL Server has a function called ISNULL():

sql
-- SQL Server
SELECT ISNULL(amount, 0) AS amount_or_zero FROM orders;

This looks like it should test "is this value NULL?" but it does NOT. SQL Server's ISNULL(expr, replacement) is a NULL-replacement function — functionally equivalent to IFNULL()/NVL()/two-argument COALESCE(). It returns expr if non-NULL, otherwise replacement.

The actual "is this value NULL?" operator, in every SQL dialect including SQL Server, is spelled with a space: IS NULL.

sql
-- Testing for NULL — the actual boolean predicate, used in WHERE
SELECT * FROM orders WHERE amount IS NULL;
-- returns Bob's row

-- Replacing a NULL with a fallback — SQL Server's function (not the same thing at all)
SELECT ISNULL(amount, 0) FROM orders;
-- returns every row's amount, substituting 0 for Bob's NULL

ISNULL(amount, 0) and amount IS NULL do completely different things despite the near-identical name — one returns a value (a NULL-replaced amount), the other returns a boolean (true/false, usable in WHERE). This is a classic interview trick question and a real-world source of confusion for developers moving between dialects, since ISNULL() doesn't even exist as a function name outside SQL Server (other dialects only have the IS NULL operator, plus their own two-argument replacement functions under different names).

Summary Table

Edge Cases

• COALESCE short-circuits: it stops evaluating arguments once it finds the first non-NULL one, which matters if a later argument is an expensive subquery.
• SQL Server's ISNULL() has a subtle additional quirk: it infers its result data type from the first argument, which can silently truncate a longer replacement string, whereas COALESCE follows standard type-precedence rules across all arguments — another reason to prefer COALESCE for portable code.
• NULL = NULL evaluates to NULL (not TRUE), which is precisely why a dedicated IS NULL operator (rather than = NULL) is required to test for NULL — this ties back to the three-valued logic covered in earlier chapters.

Key Takeaways / Interview Q&A

Q: What's the difference between COALESCE and IFNULL/NVL? A: COALESCE is standard SQL and accepts any number of arguments, returning the first non-NULL one. IFNULL (MySQL/SQLite) and NVL (Oracle) are dialect-specific shortcuts that only accept exactly two arguments, equivalent to a two-argument COALESCE.

Q: Does SQL Server's ISNULL() test whether a value is NULL? A: No — despite the name, ISNULL(expr, replacement) is a NULL-replacement function, functionally like IFNULL/NVL. The actual test-for-NULL operator, in every dialect, is IS NULL (with a space).

Q: Why can't you write `WHERE amount = NULL` to find NULL rows? A: Because NULL = NULL evaluates to NULL (not TRUE) under SQL's three-valued logic, so the condition never matches. You must use WHERE amount IS NULL instead.

Q: Name Oracle's three-argument NULL-handling function. A: NVL2(expr, valueifnotnull, valueif_null) — it branches on whether expr is NULL, unlike two-argument NVL which just supplies a replacement value.