NULL Sorting and COALESCE Compared Across Databases

1. Why NULL needs special handling in sorting and fallback values

A NULL value represents the absence of a value, not an empty string or a numeric zero, and this special role makes it particularly tricky in two everyday SQL tasks: sorting with ORDER BY and replacing missing values with a sensible default. Since NULL has no defined comparison value, every database must establish its own convention for whether NULL rows appear at the beginning or end of a sorted result set, and this convention differs noticeably between MySQL, PostgreSQL, SQL Server, and Oracle.

The landscape of functions that replace a NULL value with a fallback is similarly fragmented. The ANSI standard defines COALESCE() for this purpose, yet several databases additionally offer proprietary shorthand forms like IFNULL(), ISNULL(), or NVL(), which differ in argument count, type conversion, and short-circuit behavior. Anyone writing applications for multiple databases must clearly distinguish both topics, sorting and fallback values, since they often appear together in the same query, for example in a date-sorted list where missing dates must both be sorted correctly and displayed sensibly.

2. Default behavior: NULLS FIRST vs. NULLS LAST per database

The ANSI SQL standard itself does not prescribe a fixed default behavior for the position of NULL values in a sorted result set and explicitly leaves this decision to vendors. PostgreSQL and Oracle chose to place NULL values last by default in ascending order (ASC), as if they were larger than any other value, while in descending order (DESC) they correspondingly move to the front.

MySQL and SQL Server chose the opposite convention: here, NULL values are treated as the smallest possible value and appear first by default with ASC sorting, last with DESC sorting. This difference is one of the most commonly overlooked stumbling blocks during a migration between the two database families, since a working, untested query suddenly displays rows with missing values at a completely different position in the result list after migration.

-- PostgreSQL and Oracle: NULL sorts as if it were larger than any value
-- With ASC, NULL rows appear LAST by default
SELECT id, discount_percent FROM products ORDER BY discount_percent ASC;
-- Example order: 5, 10, 15, NULL, NULL

-- MySQL and SQL Server: NULL sorts as if it were smaller than any value
-- With ASC, NULL rows appear FIRST by default
SELECT id, discount_percent FROM products ORDER BY discount_percent ASC;
-- Example order: NULL, NULL, 5, 10, 15

3. Explicit control with NULLS FIRST/LAST where supported

PostgreSQL and Oracle offer the optional keywords NULLS FIRST and NULLS LAST directly at the end of an ORDER BY column, providing explicit control independent of the implicit default behavior. This syntax is part of the ANSI standard and allows specifying, regardless of sort direction, exactly whether NULL values should appear first or last, which ensures consistency especially in reports with mixed sort directions.

MySQL and SQL Server still do not support this syntax natively to this day, which means developers on both systems must fall back on workarounds whenever the desired behavior deviates from the respective default. This missing support is one of the most concrete cases where the four major databases diverge completely in syntax on a task that is actually covered by the standard.

-- PostgreSQL and Oracle: explicit NULLS FIRST / NULLS LAST
SELECT id, discount_percent
FROM products
ORDER BY discount_percent ASC NULLS FIRST;
-- Forces NULL rows to appear first, overriding the ASC default of NULLS LAST

SELECT id, discount_percent
FROM products
ORDER BY discount_percent DESC NULLS LAST;
-- Forces NULL rows to appear last, overriding the DESC default of NULLS FIRST

4. Workarounds for databases without native NULLS FIRST/LAST syntax

For MySQL and SQL Server, the common workaround is to add an extra computed column to the ORDER BY clause that first sorts on whether a value is NULL, and only afterward on the actual value. In MySQL, the typical approach is ORDER BY column IS NULL, column, since the boolean expression column IS NULL evaluates to 0 for non-NULL values and 1 for NULL values, which automatically pushes NULL values to the end in ascending sorting.

SQL Server has no direct IS NULL-as-integer expression in this form, so the typical approach there is a CASE WHEN column IS NULL THEN 1 ELSE 0 END as an additional sort key, functionally identical to the MySQL approach but with noticeably more code. Both workarounds are fully portable and also work on PostgreSQL and Oracle, which is why they are worth using for cross-database code even where NULLS FIRST/NULLS LAST would be available on those systems.

-- MySQL workaround: boolean expression as a secondary sort key
SELECT id, discount_percent
FROM products
ORDER BY discount_percent IS NULL, discount_percent ASC;
-- IS NULL evaluates to 0 (false) or 1 (true), pushing NULLs to the end

-- SQL Server workaround: CASE expression as a secondary sort key
SELECT id, discount_percent
FROM products
ORDER BY
  CASE WHEN discount_percent IS NULL THEN 1 ELSE 0 END,
  discount_percent ASC;

-- Portable pattern: works on all four databases without native syntax

5. COALESCE as the ANSI standard and its short-circuit evaluation

The function COALESCE(a, b, c, ...) is part of the ANSI SQL standard and is supported identically by all four databases discussed. It accepts any number of arguments and returns the first value that is not NULL, or NULL if all arguments actually are NULL. Important for performance and for side effects in expressions: COALESCE() evaluates arguments from left to right and stops as soon as the first non-NULL argument is found, a true short-circuit evaluation that does not even execute later expressions.

This short-circuit property is especially relevant when later arguments contain computationally expensive subqueries or function calls, since these are skipped on an early match. Since COALESCE() works identically across all four databases, it is the first choice for portable code, while the proprietary alternatives discussed in the next section only make sense where a deliberately database-specific optimization is desired.

-- COALESCE: identical syntax and behavior across all four databases
SELECT id, COALESCE(discount_percent, 0) AS discount_or_zero
FROM products;

-- Multiple fallback values, evaluated left to right, short-circuits early
SELECT id, COALESCE(preferred_name, display_name, username, 'Unknown')
  AS resolved_name
FROM customers;
-- If preferred_name is not NULL, display_name and username are never evaluated

6. Proprietary alternatives: IFNULL, ISNULL, NVL compared

Besides COALESCE(), each database offers its own, older, proprietary shorthand with exactly two arguments. MySQL uses IFNULL(expression, replacement), SQL Server ISNULL(expression, replacement), and Oracle NVL(expression, replacement). PostgreSQL completely forgoes such a proprietary shorthand and consistently relies on COALESCE() as the only path, which makes PostgreSQL the most consistent of the four databases in this area.

An important, often overlooked difference concerns type conversion: SQL Server's ISNULL() derives the return type from the first argument, which can lead to silent truncation when the first and second arguments have different data types, while COALESCE() in SQL Server chooses the more general type according to standard type conversion rules. This discrepancy between ISNULL() and COALESCE() within the same SQL Server system is a common, hard-to-find bug when both functions are used interchangeably without reflection.

-- MySQL: IFNULL(), exactly two arguments
SELECT id, IFNULL(discount_percent, 0) FROM products;

-- SQL Server: ISNULL(), return type inferred from the FIRST argument
SELECT id, ISNULL(discount_percent, 0) FROM products;
-- Warning: ISNULL(NULL, 'some long string') may silently truncate
-- if the inferred type from a typed NULL is shorter than the replacement

-- Oracle: NVL(), exactly two arguments, similar to IFNULL
SELECT id, NVL(discount_percent, 0) FROM products;

-- PostgreSQL: no proprietary shorthand, COALESCE() is the only native option
SELECT id, COALESCE(discount_percent, 0) FROM products;

7. NULL in comparison operators and three-valued logic briefly explained

Besides sorting and fallback values, NULL also plays a special role in direct comparisons, regulated identically across all four databases: a comparison like column = NULL never returns TRUE, but always UNKNOWN, even if the column actually contains NULL. This so-called three-valued logic with the states TRUE, FALSE, and UNKNOWN is part of the ANSI standard and consistently implemented in MySQL, PostgreSQL, SQL Server, and Oracle, which is why IS NULL or IS NOT NULL must always be used for NULL checks.

This consistency in three-valued logic stands in clear contrast to the inconsistency in sorting and fallback values and shows that the databases agree very closely on the actual ANSI core standard, while areas with historically grown, vendor-specific extensions like NULLS FIRST or proprietary fallback functions diverge much more strongly. This article deliberately covers only sorting and fallback values in detail, while the full three-valued logic in aggregate functions is reserved for a dedicated article on this blog.

8. Portable patterns for NULL handling in application code

For applications that must support multiple databases, consistently using COALESCE() instead of the proprietary shorthand forms is recommended, since it works identically on all four systems without adjustment. For the sort order of NULL values, the portable CASE WHEN column IS NULL THEN 1 ELSE 0 END approach from section four is the safest choice, since it works independently of the target database's default behavior and explicitly documents in code which behavior is actually desired.

ORMs like Doctrine, Eloquent, or SQLAlchemy already offer a coalesce() query builder method in current versions that internally generates the target database's native syntax. Not all ORMs offer a built-in abstraction for NULLS FIRST/NULLS LAST, which is why an automated test that explicitly checks the actual sort order of NULL values against every supported target database, rather than relying on implicit default behavior, is worthwhile here.

9. NULL-handling functions compared directly

The following table summarizes the key differences in sorting and fallback functions.

A striking pattern is the clear split: PostgreSQL and Oracle share both the default behavior (NULL last with ASC) and native NULLS FIRST/NULLS LAST support, while MySQL and SQL Server both rely on the opposite default behavior and the same workaround.

10. Summary

NULL behavior in sorting differs clearly between database families: PostgreSQL and Oracle sort NULL last by default in ascending order and offer native NULLS FIRST/NULLS LAST control, while MySQL and SQL Server place NULL first by default and rely on a CASE or IS NULL workaround. For fallback values, COALESCE() is the only function that works identically on all four systems, while IFNULL(), ISNULL(), and NVL() are proprietary, non-interchangeable shorthand forms.

For portable code, consistently using COALESCE() instead of the proprietary alternatives and an explicit, tested sorting workaround instead of relying on implicit default behavior pays off. Knowing these NULL differences in sorting and fallback values prevents silent behavior changes during database switches that would otherwise only surface through faulty reports or incorrectly sorted lists.

11. FAQ: NULL Sorting and COALESCE Compared Across Databases