How Do You Sort Nulls?

Not all systems order nulls the same way. SQL-92 specifies that when nulls are sorted, they should be either greater than or less than all non-null values. Which you get depends on your implementation.

SQL VARIANTS

In Microsoft SQL Server, NULL is less than all non-null values, but in Sybase SQL Anywhere, NULLs are grouped together at the beginning of the display, whether the sort is ascending or descending. Oracle sorts NULL last. Here's one query, with different displays from each of these three systems:

SQL
select title_id, pub_id, price * ytd_sales as income, price
from titles
where type = 'business' or price is null
order by price
MS SQL Server
 title_id pub_id income price -------- ------ --------------------- ------------------- ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial