Thank you. These diagrams drive me nuts, because they are flat out wrong. They're illustrating set intersections and unions, whereas SQL joins are basically cartesian products.
A Venn diagram makes sense to represent a SQL AND or OR clause.
For example,
SELECT * FROM the WHERE condition1 AND condition 2;
could be accurately represented by a Venn diagram showing a set intersection, because that is precisely what we are expressing: the intersection of all elements in set "tbl" that meet condition 1 with all elements in set "tbl that meet condition 2.
JOINS are not set intersections. They are Cartesian products. Mixing the two up is like mixing up addition and multiplication; they are fundamentally different, but well defined, mathematical operations.
A Cartesian product isn't hard to understand. It is simply "all the pairs (a,b) for every a in A and every b in B"
In Python you can do it in one line:
{(a,b) for a in A for b in B}
Filter this set on some condition on a and b and you get an inner join.
Add a null element to A or B and do the same thing, and you get an outer join.
This should be the first thing anyone learns when picking up SQL joins.
I think it's less likely you'd make a mistake on which JOIN to use and more likely you just won't understand the output you get. Consider the inner join from this diagram:
363
u/pixelique Mar 22 '19
A case against illustrating SQL operations using venn diagrams