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.