re: SQL 101: All about SQL JOINs VIEW POST

VIEW PARENT COMMENT VIEW FULL DISCUSSION
 

That's not at all true. They're very rare in my experience, but even GOTO has its place and there's been much less harm wrought by full joins.

For an example, a data model I'm currently working with involves a 1:1 relationship between stations and sites: a station can be deployed to a site, a site may be empty (no station), or a station may be held in reserve (no site). I want to see all my stations and all my sites, with deployed stations correctly lined up with their sites. A full outer join between stations and sites on stations.site_id produces exactly the desired output. Anything else would be clumsy at best, and the only remotely appropriate alternative model simply moves the foreign key to the other side.

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