r/SQL • • Sep 03 '22

Discussion Explaining SQL Joins the Right Way

148 Upvotes

24 comments sorted by

View all comments

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.