2

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?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
David542
  • 104,438
  • 178
  • 489
  • 842
  • People tend to avoid `RIGHT JOIN`, because they are somehow counter-intuitive (at least for people that read from left to right!). But bottom line that's more a matter of taste, which, to me, qualifies the question as opinion-based. – GMB Aug 31 '20 at 23:12
  • @GMB . . . It is not just a matter of taste. SQL parses from left-to-right and `A LEFT JOIN B LEFT JOIN C` can be subtly different from `C RIGHT JOIN B RIGHT JOIN A` depending on the conditions. – Gordon Linoff Aug 31 '20 at 23:13

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • also, what is a `lateral join` ? i've never seen that before -- what rdbms? – David542 Aug 31 '20 at 23:28
  • 1
    @David542 . . . Lateral joins are part of the SQL standard, although the syntax varies (SQL Server uses `cross apply`, for instance). Postgres, Oracle, SQL Server, the most recent version of MySQL, PrestoDB, and Snowflake (and perhaps others) support them. – Gordon Linoff Sep 01 '20 at 00:23
  • is this the official SQL standard? https://pubs.opengroup.org/onlinepubs/9695959099/toc.pdf Or is there another site you can link to that contains the SQL standard? – David542 Sep 01 '20 at 19:26
0

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

RxGianYagami
  • 76
  • 1
  • 10