SQL Join: Double iteration with a filter not a Venn Diagram

Christopher Diep
2 min readDec 4, 2018

Venn diagrams show up whenever I do a quick Google image search on SQL join.

Google image search on sql join

However, it’s technically not how JOIN works. I suppose blogs usually get away with that explanation because the majority of queries usually join on unique id’s.

SELECT *
FROM Orders
INNER JOIN Products ON Orders.product_id = Products.id;

How does Inner Join really work then?

The Inner Join (part of the subset of Cross Join) is equivalent semantically to a double iteration with a filter. (Under the hood, it is more efficient. Let’s not get into that now.)

https://www.w3resource.com/sql/joins/cross-join.php

Here’s some pseudocode that reinforces the idea behind the diagram and let’s go back to the product and order example:

for order in orders
for product in products
return * if order.product_id = product.product_id

Doesn’t that change your world? I know it did change mine. I have to readjust my perception of JOIN.

Real life example: Deck of Cards

There are 4 suits. There are 13 card ranks. There is a total of 52 cards. (Double iteration math is just multiplying the number of records to go through.)

https://en.wikipedia.org/wiki/Cartesian_product

Then comes the filter… which is how blogs can get away with it.

Conclusion

Venn diagrams are not completely accurate when explaining how SQL Join works. Try to go with the double iteration and filter explanation. It is not too difficult.

At the very least, preface the Venn diagram explanation with how it is not truly how it works. (I felt similarly when I started to learn how chemical bonds work and how the Lewis dot structure is not truly an accurate representation of the world. It is a pretty good model though.)

RESOURCES

--

--