MySQL / PostgreSQL Order By with Nullable column

Tests

Firstly we need to create some iconic test data :)

MySQL

create table users (
	id bigint NOT NULL AUTO_INCREMENT, 
	name VARCHAR(50), 
	birth_date DATETIME, 
	PRIMARY KEY (id)
);

PostgreSQL

create table users (
	id SERIAL PRIMARY KEY, 
	name VARCHAR(50), 
	birth_date TIMESTAMP WITHOUT TIME ZONE
);

MySQL / PostgreSQL

insert into users (name, birth_date) 
values ('Arnold Schwarzenegger', '1947-06-27 10:00:00');

insert into users (name, birth_date) 
values ('Bruce Willis', '1955-03-19 00:00:00');

insert into users (name, birth_date) 
values ('Sylvester Stallone', '1946-06-06 00:00:00');

insert into users (name) 
values ('John Doe');

Let's see what we have.

select * from users;

+----+-----------------------+---------------------+
| id | name                  | birth_date          |
+----+-----------------------+---------------------+
|  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00 |
|  2 | Bruce Willis          | 1955-03-19 00:00:00 |
|  3 | Sylvester Stallone    | 1946-06-06 00:00:00 |
|  4 | John Doe              | NULL                |
+----+-----------------------+---------------------+

Ok, let's try to order by birth_date column, which is NULL for Mr. John Doe.

MySQL

select * from users order by birth_date

+----+-----------------------+---------------------+
| id | name                  | birth_date          |
+----+-----------------------+---------------------+
|  4 | John Doe              | NULL                |
|  3 | Sylvester Stallone    | 1946-06-06 00:00:00 |
|  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00 |
|  2 | Bruce Willis          | 1955-03-19 00:00:00 |
+----+-----------------------+---------------------+

Let's try DESC sorting

select * from users order by birth_date desc

+----+-----------------------+---------------------+
| id | name                  | birth_date          |
+----+-----------------------+---------------------+
|  2 | Bruce Willis          | 1955-03-19 00:00:00 |
|  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00 |
|  3 | Sylvester Stallone    | 1946-06-06 00:00:00 |
|  4 | John Doe              | NULL                |
+----+-----------------------+---------------------+

PostgreSQL

select * from users order by birth_date

 id |         name          |     birth_date
----+-----------------------+---------------------
  3 | Sylvester Stallone    | 1946-06-06 00:00:00
  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00
  2 | Bruce Willis          | 1955-03-19 00:00:00
  4 | John Doe              |

and for DESC sorting

select * from users order by birth_date desc

 id |         name          |     birth_date
----+-----------------------+---------------------
  4 | John Doe              |
  2 | Bruce Willis          | 1955-03-19 00:00:00
  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00
  3 | Sylvester Stallone    | 1946-06-06 00:00:00

As you can see, the absolutely same queries on MySQL and PosgreSQL return slightly different results.

The problem

MySQL interprets NULL as a smallest value, while PostgreSQL interprets it as the biggest one.

Mathematically, comparison is an operation of subtraction first operand from second one, but NULL is an unknown value and we can't compare any value with unknown.

This is why we have what we have :)

Solution

What if we need to sort NULL value in a different way? Fortunatelly both databases have IS NULL and IS NOT NULL constructions.

Let's try to update our query:

MySQL

select * from users order by birth_date is null, birth_date;

+----+-----------------------+---------------------+
| id | name                  | birth_date          |
+----+-----------------------+---------------------+
|  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00 |
|  2 | Bruce Willis          | 1955-03-19 00:00:00 |
|  3 | Sylvester Stallone    | 1946-06-06 00:00:00 |
|  4 | John Doe              | NULL                |
+----+-----------------------+---------------------+

select * from users order by birth_date is not null, birth_date;

+----+-----------------------+---------------------+
| id | name                  | birth_date          |
+----+-----------------------+---------------------+
|  4 | John Doe              | NULL                |
|  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00 |
|  2 | Bruce Willis          | 1955-03-19 00:00:00 |
|  3 | Sylvester Stallone    | 1946-06-06 00:00:00 |
+----+-----------------------+---------------------+

PostgreSQL

select * from users order by birth_date is null, birth_date;

 id |         name          |     birth_date
----+-----------------------+---------------------
  3 | Sylvester Stallone    | 1946-06-06 00:00:00
  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00
  2 | Bruce Willis          | 1955-03-19 00:00:00
  4 | John Doe              |

select * from users order by birth_date is not null, birth_date;

 id |         name          |     birth_date
----+-----------------------+---------------------
  4 | John Doe              |
  3 | Sylvester Stallone    | 1946-06-06 00:00:00
  1 | Arnold Schwarzenegger | 1947-06-27 10:00:00
  2 | Bruce Willis          | 1955-03-19 00:00:00

In this case both databases operate with boolean values as a result of IS NULL and IS NOT NULL and now it's possible to sort it in a logical way.