News for hackers. Stuff that matters.
Re:Let's Talk about Joins by recursive on Saturday January 20, 2024 @11:52PM (#39075679)
Consider a LEFT JOIN b RIGHT JOIN c. You'll get all the records from c.

Now consider b LEFT JOIN a LEFT JOIN c. The result is not the same at all.

Re:Let's Talk about Joins by winternewt on Sunday January 21, 2024 @10:11AM (#39079127)
c LEFT JOIN (a LEFT JOIN b) is the same.
Re:Let's Talk about Joins by recursive on Sunday January 21, 2024 @11:17AM (#39079797)
I didn't know this was syntactically valid, but maybe it is.
Re:Let's Talk about Joins by winternewt on Sunday January 21, 2024 @11:34AM (#39079954)
It most definitely is. A join is a binary operator and hence has only two operands. Joining three tables requires an associativity rule, and joins are left associative. The ability to omit braces could arguably be considered syntactic sugar as well. In other words

  a LEFT JOIN b RIGHT JOIN c
is equivalent to

  (a LEFT JOIN B) RIGHT JOIN c.
Once you have that, you can flip the RIGHT JOIN by swapping the operands, giving you

  c LEFT JOIN (a LEFT JOIN b).
Re:Let's Talk about Joins by recursive on Sunday January 21, 2024 @10:44PM (#39085941)
SQL grammar could easily specify that braces are just not allowed. In fact, I thought that was the case. Now that I know they're allowed, now I agree that right joins have no reason to exist.
Re:Let's Talk about Joins by andy81 on Monday January 22, 2024 @01:27PM (#39093246)
A CTE would work too.
Re:Let's Talk about Joins by jayknight on Monday January 22, 2024 @07:16PM (#39097523)
left join requires an `on` clause, which can feel out of place when doing it like this, but makes sense when you consider the parenthesized join to be a relation itself

  c left join (  
    a left join b on a.a_id = b.a_id  
  ) on c.a_id = a.a_id
The parenthesis syntax is also super useful when you want to left join to a group of tables that all need to inner join together:

  a left join (
    b inner join c 
     on b.b_id = c.b_id
    inner join d
      on c.c_id = d.c_id
    inner join e
      on d.e_id = e.e_id
  )
  on a.b_id = b.b_id
that way, the b/c/d relation is all or nothing instead of the possibility of get values from b but not c and d.