CODEWITHSUNDEEP
sql
David542
David542

Reputation: 110203

Usage of [Right] .. JOIN in practice

This question is related to:

I was wondering if in practice anyone has written or come across the usage of a RIGHT JOIN that is useful? If so, what was the scenario? I have not really myself (outside of seeing examples of "look at this RIGHT join"), so was wondering if this is actually used in practice, and if so, what was the context?

Upvotes: 2

Views: 61

Answers (2)

RxGianYagami
RxGianYagami

Reputation: 76

actually there's nothing specific different practice between LEFT JOIN and RIGHT JOIN but clause sentence arrangement. back in definition of SQL it is just statement arrangement for order command to your DBMS.

if you use RIGHT JOIN clause, the data will served more inclined to table on the Right (which is mentioned after that JOIN clause), using LEFT JOIN makes the opposites from RIGHT JOIN

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Consider a situation where I need a lateral join to get the join key for a column (say because the values are stored in an array). Then I want to outer join to a fixed list of codes, say to count them. I could write this as:

select c.code, count(u.code)
from codes c left join
     (t cross join lateral
      unnest(codes) u(code)
     )
     on c.code = u.code;

Or by using a right join:

select c.code, count(u.code)
from t cross join lateral
     unnest(codes) u(code) right join
     codes c
     on c.code = u.code;

I still prefer the left join version, but some might reasonably prefer avoiding parentheses in the from clause.

If you have only two tables in the from clause, left join and right join are interchangeable; this is not always true when there are more than two tables. I'm not sure if reading direction for the alphabet would affect which outer join someone prefers. I would hypothesize that the underlying grammar of the user's native language might have an effect, perhaps languages where the object precedes the subject; such languages are rather rare, though, and not among the more commonly spoken languages.

Upvotes: 3

Related Questions