re: SQL 101: All about SQL JOINs VIEW POST

VIEW PARENT COMMENT VIEW FULL DISCUSSION

Well, I am not a SQL architect by any mean, but a join table sites_to_stations would not only allow to avoid FULL JOIN but also attach some attributes to links (like linked_at or like) in the future for free.

I already get metadata for free (it's event sourced), so a junction table even with the appropriate constraints to enforce 1:1 vs m:m would just take up space and make other queries more complicated.

Got it. Thanks for the explanation, I was wrong. In this case it indeed sounds as the best solution.

Also, the junction table approach still doesn't give me what I want, since the un-junctioned records collide on the join criteria:

[local] dian#dian= select * from left_table;
 id   val  
────┼───────
  1  one
  2  two
  3  three
(3 rows)

Time: 0.559 ms
[local] dian#dian= select * from right_table;
 id  val 
────┼─────
  1  1
  2  2
  3  3
(3 rows)

Time: 0.587 ms
[local] dian#dian= select * from left_right;
 left_id  right_id 
─────────┼──────────
       1         3
       3         1
(2 rows)

Time: 0.533 ms
[local] dian#dian= select * from left_table l
left outer join left_right lr on lr.left_id = l.id
right outer join right_table r on r.id = lr.right_id;
   id     val    left_id  right_id  id  val 
────────┼────────┼─────────┼──────────┼────┼─────
      1  one           1         3   3  3
      3  three         3         1   1  1
 (null)  (null)   (null)    (null)   2  2
(3 rows)

Time: 0.984 ms

To get unaffiliated lefts and rights to show up, you have to run the query twice and union the results:

[local] dian#dian= select * from left_table l
dian#dian- left outer join left_right lr on lr.left_id = l.id
dian#dian- left outer join right_table r on r.id = lr.right_id
dian#dian- union
dian#dian- select * from left_table l
dian#dian- left outer join left_right lr on lr.left_id = l.id
dian#dian- right outer join right_table r on r.id = lr.right_id;
   id     val    left_id  right_id    id     val   
────────┼────────┼─────────┼──────────┼────────┼────────
      3  three         3         1       1  1
 (null)  (null)   (null)    (null)       2  2
      2  two      (null)    (null)  (null)  (null)
      1  one           1         3       3  3
(4 rows)

Time: 0.697 ms

Indeed. This is a perfect example of how blinkered are we (I :) might be with our background, experience and expertise.

Say, 20 times in my life I saw FULL JOIN in the legacy DBs, that I always could have refactored to the joined tables. I never met the example of a kind you just shared (btw, thank you again and again for taking time doing that, I really appreciate and value it.)

And—voilà—I stupidly made the wrong assumption based on my experience, expertise and all that crap.

Very enlightening.

You're welcome! :) I should say that the case I mentioned with sites and stations is, to my memory, one of maybe two or three times I've actually written a full join in application code over more than a decade of designing databases and writing SQL. It's really rare, but an important tool nonetheless.

code of conduct - report abuse