Sql how to make null values come last when sorting ascending

sql

null-handling

sql-queries

database-performance

byAnton Shumikhin·Sep 7, 2024

If you want to ensure NULLs appear last when sorting in ascending order, use ORDER BY with a CASE expression:

ORDER BY CASE WHEN column_name IS NULL THEN 1 ELSE 0 END, column_name;

This approach assigns a higher sort value to NULLs. This means the true values take precedence and the NULLs fall to the bottom.

Different methods for different needs

The "Fast Answer" section provided an immediate solution, but depending on your use case, there may be more efficient methods.

PostgreSQL's built-in tool: `NULLS LAST`

With certain SQL variants like PostgreSQL, you can specifically tell the query engine to list NULLs last:

ORDER BY column_name ASC NULLS LAST;

Handling numeric and string NULLs

When managing numeric values, you can set NULLs to a higher value using ISNULL or COALESCE:

-- It's like giving NULL a participation trophy!
ORDER BY ISNULL(numeric_column * 0, 1), numeric_column;

For string columns, simply use ISNULL with the LEFT function:

-- Because NULL needs attention too!
ORDER BY ISNULL(LEFT(string_column, 0), 'a'), string_column;

Tackling date columns

When dealing with date columns, convert them to nullable integers for accurate sorting:

-- Let's convert our dates to numbers. Congrats, you're 737821 now!
ORDER BY ISNULL(CONVERT(INT, date_column) * 0, 1), date_column;

Simplified syntax

If you prefer simplicity over verbosity, the following command will give the same outcome:

-- Because life is hard and we want simplicity!
ORDER BY column_name IS NULL, column_name;

These sort expressions provide optimum control over data output without interfering with the intrinsic values.

Case-by-case advice

Accommodating diverse sorting behaviors like handling data types and quirks in DBMS is crucial to mastering NULL handling.

Safeguarding against sorting issues

While heuristics generally work, make sure to anticipate potential hiccups, like arithmetic overflow or type conversion errors. These can be you uninvited party crashers!

Performance matters

With big data, performance becomes a concern. Using CASE or ISNULL in ORDER BY may slow things down, like a lazy turtle. Consider indexing the column in question or using persisted computed columns.

Catering to different SQL flavors

Slight adjustments are required for various SQL flavors like pizza toppings. For example, Oracle fans, use NVL instead of ISNULL or COALESCE. It's not the same, but it tastes good!

Further down the rabbit hole

Beware of coercion

Applying ISNULL or COALESCE can lead to type coercion, changing the natural sorting dance. Always dance with the one that brought you, i.e., use a placeholder that matches your column's type closely.

The NULL-index conundrum

NULL values in indexed columns can make ORDER BY choke, negating the performance benefits. It’s crucial to find a balance between sorting goals and performance boosts.