MySQL Order by id, unless the record has a parent_id

I’m using Laravel (and MySQL of course) to build a pages module, a page can have 1 parent

This is my current data, and using order_by="id" it’s also returned like this.

+----+-----------+
| id | parent_id |
+----+-----------+
|  1 | NULL      |
|  2 | NULL      |
|  3 | 1         |
|  4 | 1         |
+----+-----------+

Now I’m wondering if it’s possible to have a result set where the children are ordered underneath their parent. Which would render the following result

+----+-----------+
| id | parent_id |
+----+-----------+
|  1 | NULL      |
|  3 | 1         |
|  4 | 1         |
|  2 | NULL      |
+----+-----------+

Thoughts

Maybe it is possible to generate an order_by query that orders by id, but assigns a temporary id in a temp column, in between the others, in case the row has a parent_id?

So ideally the 2 children (3, 4) of the parent (1) are shown just below it.

Answer

You can use coalescefunction:

ORDER BY coalesce(`parent_id`,`id`),`parent_id` is not null

P.S. ,parent_id is not null is needed for the case even (2,1) inserted instead of (2,null) in the order of VALUES(2,1),(1,null),(3,1),(4,1)(special thanks to @splash58)

Demo