CODEWITHSUNDEEP
sqlpostgresqlleft-joinrelational-algebraright-join
Gregzor
Gregzor

Reputation: 302

Rewrite multiple right joins to left joins

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.

Upvotes: 3

Views: 850

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 4

Related Questions