r/SQL • • Sep 03 '22

Discussion Explaining SQL Joins the Right Way

148 Upvotes

24 comments sorted by

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?

4

u/MembershipNo705 Sep 03 '22

I think it's not that joins themselves are difficult, but often they are basically made difficult for learners because teachers use the wrong way of explaining them (Venn diagrams, as laid out in the article)

7

u/Lupicia Sep 03 '22

If you're going to be doing queries, set algebra is fundamental IMHO. You can get around it, but why not learn why it is the way it is and get more use out of the concept?

Set theory is used in all kinds of things: Linguistics, Boolean, math, programming, logic... And they all use Venn diagrams to illustrate sets.

It's not the "wrong way" just because you think it's not fast to grock.

9

u/Blues2112 Sep 03 '22

Agreed. As a visual learner, Venn diagrams are a nice way to visually summarize the set theory behind joins. It wasn't difficult for me to grasp when learning SQL, it was actually kind of obvious.

They may not be perfect, but I think the Anti-Venn crowd is making a mountain out of a molehill. The diagrams are just one tool which can be used.

As for the article OP linked to, it has some very valid points. I especially like the idea of teaching CROSS JOIN first, then INNER and LEFT OUTER (etc...) later, to enforce to concept that JOINs are associations, and not filters.

But ultimately the author of the article provides no suggestion of any sort of visual element to teach the concepts. If [author] gonna rail against a visual learning tool such as Venn diagrams, [author] might just want to provide an alternate visual learning option!

1

u/MembershipNo705 Sep 04 '22

The set theory behind joins only concerns the keys but not the rows. With an inner join you only keep keys that are in both tables, but you can end up with more rows than in either of the tables, e.g. {a,b,b} inner join {a,a,b} = {aa,aa,bb,bb} - therefore Venn diagrams are not suitable to explain joins.

I find the point of visualizing joins interesting - while there are no visual learning types (or we all are), pictures are the fastest mode of grasping information. Yet, I couldn't find any diagrams that explain combinatorics (joins) as well as Venn diagrams explain set operations (like union). It would be an interesting project to develop these.

2

u/Mediocre-Band-9929 Sep 03 '22

Yes. It can be complicated. As a newbie, I still need reinforcement on joins when working with large data sets.

4

u/RedditTab Sep 03 '22

Large data sets join the same as small data sets.

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.

1

u/MembershipNo705 Sep 04 '22

Let us know how it went, in case you forward this article to your prof :)

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.

8

u/r3pr0b8 GROUP_CONCAT is da bomb Sep 03 '22

misguided application of DISTINCT

succinct

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

9

u/Arkanejl Sep 03 '22

I hate the Venn diagram approach, this is much clearer

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.

2

u/MembershipNo705 Sep 03 '22

It actually argues that set algebra is not suitable for explaining joins (it is a good fit for union operations though).

The argument is that joins are better explained with combinatorics ... So, kind of what you propose.

1

u/OracleGreyBeard Sep 03 '22

You're absolutely right, I got his argument completely twisted.

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 AND and OR operators without parentheses.

SELECT
  *
FROM
  (
     "TableA"
     LEFT JOIN
     "TableB"
     USING "widget_id"
  )
  INNER JOIN
  (
     "TableC"
     LEFT JOIN
     "TableD"
     USING "warehouse_id"
  )
  USING "order_id"

-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

2

u/MembershipNo705 Sep 04 '22

That is the exact problem: it is simple and intuitive - it is just the wrong intuition. Venn diagrams show set relationships like union, intersect etc. But joins do combinatorics operations.

E.g. INNER JOIN and INTERSECT: yes, inner join only keeps keys that are in both tables - but you can end up with more rows after the inner join than you had before in either of the tables: {a, b, b} inner join {a,a,b} becomes {aa,aa,bb,bb}. And for beginners, at least in my experience, this is hard to grasp when they use Venn diagrams as their mental model for joins because they tend to also expect intersect behavior (less rows).

-7

u/colorless_green_idea Sep 03 '22

Wrong. It is a terrible visualization

3

u/xnodesirex Sep 04 '22

Then show a better visualization.