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.

Gordon Linoff · Accepted Answer · 2015-07-12T14:03:01.780

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.

Rewrite multiple right joins to left joins

1 Answers1