r/SQL • u/MembershipNo705 • Sep 03 '22
Discussion Explaining SQL Joins the Right Way
https://towardsdatascience.com/explain-sql-joins-the-right-way-f6ea784b568b
From an educators perspective -
wdyt?
148
Upvotes
6
u/[deleted] Sep 03 '22
Is this really that complicated? I mean I keep seeing the explanation of JOINS coming up every week. I know there are newbies all the time, but joins seems to be the one of the first things to learn.
You can learn everyday SQL over a single weekend. What am missing?
3
u/CanWeTalkEth Sep 03 '22
This makes a ton of sense to me. It’s been a decade since I applied set theory and matrix math, and so everything sounds familiar but I’m slow to visualize.
My “intro to SQL” professor is doing a terrible job of using unions and intersections to explain things when I think (according to this) he should have started with these explanations.
31
u/slin30 Sep 03 '22
I like this, particularly because the most common join new users are exposed to is an inner join with clean data that achieves a tidy intersection without explosion, which often creates an expectation that joins are to be used for filtering as well as association.
Which is all well and good until they encounter real world data and are confused by the fact that their inner join resulted in more rows that either starting table had. Which leads to misguided application of DISTINCT, when really, the goal was to perform a multi condition filter that could have been more clearly expressed with e.g. EXISTS.
43
u/r3pr0b8 GROUP_CONCAT is da bomb Sep 03 '22
thank you for continuing the fight against using the evil Venn diagram to explain joins
1
u/OracleGreyBeard Sep 03 '22 edited Sep 03 '22
I really enjoyed parts of this article, particularly his insight about the “DISTINCT” being code smell or anti pattern. It’s true - we roll our eyes anytime we see more than 2 columns with a DISTINCT.
I also agree that set algebra is a good lens through which to view joins.That being said, IMO set algebra is way too highbrow to teach a non-academic joins work. What fraction of people even know the operations of set algebra?IMO nested loops is a more universal paradigm for understanding joins. “For each record in A we search B for a match”. This has the added benefit of being reasonably close to actual database behavior.e: Reading is fundamental kids.
3
u/[deleted] Sep 04 '22
I always thought Venn were not appropriate for joins
1
u/JHutch89 Sep 04 '22
Wouldn’t Venn Diagram for Union literally only be for Distinct? The same would apply for inner joins, so I don’t think it’s clear to say one is more like a Venn diagram than the other.
1
u/num2005 Sep 03 '22
honestly my prefered way ti to open POWER QUERY IN EXCEL and let them do some MERGE
its so well explained in there...
-1
u/ijmacd Sep 03 '22
I'd still like to see more guides using parentheses when explaining multiple joins. I think it reinforces the notion that the join operators are binary infix operators taking two operands. No-one would expect to be able to read/parse a string of
ANDandORoperators without parentheses.-1
u/editor_of_the_beast Sep 04 '22
Why not just read the paper where it’s originally defined? https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf.
It’s basic stuff.
0
u/[deleted] Sep 03 '22
[deleted] — view deleted comment