Rewrite multiple right joins to left joins

Question

I read that

SELECT * FROM table_0 
    RIGHT JOIN table_1 ON table_0.col_0 = table_1.col_0;

is the same as:

SELECT * FROM table_1
    LEFT JOIN table_0 ON table_0.col_0 = table_1.col_0;

How do I rewrite a longer query, let's say:

SELECT * FROM  table_0
    RIGHT JOIN table_1 ON table_1.col_0 = table_0.col_0
    RIGHT JOIN table_2 ON table_2.col_1 = table_1.col_1
    RIGHT JOIN table_3 ON table_3.col_2 = table_2.col_2;

to use only LEFT JOINS? I would be interested in a generic solution to such problem.

I'm specifically interested in PostgreSQL dialect if it does matter. From the explain output I can see that simply replacing LEFT to RIGHT is not enough here. The queries return different number of rows.

Answer 1

Just reverse the tables:

SELECT *
FROM table_3 LEFT JOIN
     table_2
     ON table_3.col_2 = table_2.col_2 LEFT JOIN
     table_1
     ON table_2.col_1 = table_1.col_1 LEFT JOIN
     table_0 
     ON table_1.col_0 = table_0.col_0;

A LEFT JOIN keeps all tables in the first table, regardless of whether the condition evaluates to true, false, or NULL. A RIGHT JOIN keeps all rows in the second table, regardless of the condition.

I want to note that this is true for the ON conditions as you have written them. It might not be true for all ON conditions.