News for hackers. Stuff that matters.
Re:Let's Talk about Joins by andy81 on Saturday January 20, 2024 @02:02PM (#39071055)
If I was going to add anything -

"Vertical join" is normally called union.

"Right join" is just (bad) syntax sugar and should be avoided. Left join with the tables reversed is the usual convention.

The join condition for inner is optional- if it's always true then you get a "cross join". Can be useful to show all the possible combinations of two fields.

Re:Let's Talk about Joins by Little_Kitty on Saturday January 20, 2024 @04:15PM (#39072410)
Of the tens of thousands of queries I've written I've needed right join the exactly once. It's a feature which is neat in that it exists, but the prevalence in teaching materials is entirely unjustified. Cross joins are massively more practical and enable some efficient transformations, but are usually taught only as all to all without a clear position on why they are useful.
Re:Let's Talk about Joins by Terr_ on Saturday January 20, 2024 @10:04PM (#39075131)
> , but the prevalence in teaching materials is entirely unjustified.

I agree that it is seldom good query-writing practice, but I think it makes sense in education because it rounds out the join variations to name them symmetrical, and thus easier to remember.

Re:Let's Talk about Joins by wood_spirit on Saturday January 20, 2024 @03:17PM (#39071839)
All the other non-left joins are just syntactic sugar and can be expressed using only left join…?
Re:Let's Talk about Joins by tzot on Saturday January 20, 2024 @04:13PM (#39072397)
> All the other non-left joins are just syntactic sugar and can be expressed using only left join…?

A “vertical join” (SQL UNION) is one of the “non-left joins”. How can you transform a “vertical join” to a “left join”?

Re:Let's Talk about Joins by cbreezyyall on Saturday January 20, 2024 @04:49PM (#39072768)
This feels like an interesting interview question. I think you could simulate this with a full outer join on the entire select list and coalesce? For a UNION ALL you could put some literal column in the selects from both tables that you set to different values and include that in the join so you'd get a result set that will have all nulls in the right table columns for the rows in the left table and vice versa. Something like

  WITH top_t AS (
   SELECT
     a
    ,b
    ,c
    ,'top' as nonexistent_col
   FROM table_1
  ), bottom_t AS (
   SELECT
     a
    ,b
    ,c
    ,'bottom' as nonexistent_col
   FROM table_2
  )
  SELECT
    COALESCE(top_t.a, bottom_t.a) AS a
   ,COALESCE(top_t.b, bottom_t.b) AS b
   ,COALESCE(top_t.c, bottom_t.c) AS c
  FROM top_t FULL OUTER JOIN bottom_t 
    ON top_t.a = bottom_t.a
    AND top_t.b = bottom_t.b
    AND top_t.c = bottom_t.c
    AND top_t.nonexistent_col = bottom_t.nonexistent_col -- remove this for a normal UNION
Re:Let's Talk about Joins by wood_spirit on Saturday January 20, 2024 @05:42PM (#39073224)
Bravo!!
Re:Let's Talk about Joins by tzot on Monday January 22, 2024 @04:45AM (#39087860)
This is a nice trick using full outer join! My question to wood_spirit still stands, though.
Re:Let's Talk about Joins by wood_spirit on Monday January 22, 2024 @10:48AM (#39090709)
A fun challenge :)

Assuming a null we can use as sentinel:

      SELECT COALESCE(a.col, b.col) AS col
      FROM a
      LEFT JOIN b ON (TRUE)
      WHERE (a.col IS NULL) IS DISTINCT FROM (b.col IS NULL)

(Getting that sentinel might take effort, depending on eg whether there are useful window functions. Here is a way to do it with only left joins and the assumption the column has no duplicate values:

  WITH crossed AS (
     SELECT * FROM UNNEST([1, 2]) AS sentinal
  )
  SELECT IF(sentinal = 1, col, NULL) AS col
  FROM a
  LEFT JOIN crossed ON TRUE
  WHERE sentinal = 1 OR col = (SELECT * FROM a LIMIT 1)
Re:Let's Talk about Joins by recursive on Saturday January 20, 2024 @09:20PM (#39074865)
How would you rewrite a left join followed by a right join? I don't think right joins are always sugar.
Re:Let's Talk about Joins by jayknight on Saturday January 20, 2024 @09:32PM (#39074928)
Make the "middle" table first and do to left joins from that one to the other two
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.