I really don't get the confusion around JOINs...
Any JOIN is what the author is calling a "CROSS JOIN", with "ON" (which is optional) basically being a WHERE condition, and "LEFT", "RIGHT", "INNER", "OUTER" words only describing whether or not to drop a result if the left/right side of the join would be a NULL reference.
And, of course, a "UNION JOIN" is not a join at all, because nothing is joined.
I taught myself SQL on a need-to-know basis. One thing that blew my mind and really helped making certain non-trivial queries more manageable was when I realized I could join subqueries. It never occurred to me to try, until one day I did and it worked.
A contrived example for illustration:
select oh.oid, oh.order_ref, ol.num_lines, ol.total_value
from order_head oh
join (
select oid, count() as num_lines, sum(value) as total_value
from order_lines
where status = 'ready'
group by oid
) ol on ol.oid = oh.oid
where total_value > 500
It's a nice visualization, but it assumes the keys on both sides are unique. Usually the key on at least one side of a join is not unique, and sometimes it's not unique on either side. The behaviour in these cases is an important part of the behaviour of the overall join.
It's a bit dangerous to visualize Union like that, people might confuse it with concatenation which it's not.
You'll want to use "UNION ALL" if your intent is to concatenate the two sets of rows, and not to calculate the (deduplicated) union. If only because it's much faster if your database doesn't have to check for duplicates.
I've always liked Jeff Atwoods visual explanation of joins [1]
[1] https://blog.codinghorror.com/a-visual-explanation-of-sql-jo...
https://i.pinimg.com/originals/0e/ae/a1/0eaea116051e07f24314...
Still the best tutorial.
So much better than the usual Venn diagram mess.
Props to the author
Not discussed in this article: 1-many, 1-1 and many-many relationships. Also: Fan traps and chasm traps. These issues are important to understand, to avoid serious mistakes.
Just google "sql joins diagram" and you will be rewarded by a quick one image visual representation of each type of join. Here is one in stackoverflow: https://stackoverflow.com/questions/13997365/sql-joins-as-ve...
That is a smooth site. I like how you can suggest edits to their articles right in google docs.
What framework/service is being used to build it?
Off Topic slightly and perhaps deserves a "Ask HN:" post - but I'd love any recommendations for best resources to quickly get to "medium proficiency" with SQL. Thanks!
Good, clear explanation of these SQL joins. Works well as a quick way to mentally visualize what each join would do in your own implementation.
Thanks for the learning resource.
Nice visualization. Never heard/used cross join. What would be use case for something like that in practice?
I like this one page visual for explaining joins much more succinctly https://www.codeproject.com/KB/database/Visual_SQL_Joins/Vis...
Missed self join
This seems like a terrible explanation.
Firstly, joins are substantially orthogonal to keys. You can join two relations with no keys at all. Keys might help you make sure that there are going to be results to your join, but they are not required, and i wouldn't start with them.
Secondly, union is not a join. If mentioned at all, it should be in an addendum about other ways to combine tables.
Thirdly, left and right joins are specific kinds of outer join. Surely it's much clearer to describe "left, right, and full outer joins" than "left, right, and outer joins".
Fourthly, surely the most logical way to explain joins is the progressive refinement cross -> inner -> equi -> natural -> full outer -> left/right outer. A cross join is a Cartesian product, simple to explain. Restrict that with a predicate and you have an inner join (i'd use an example where the predicate is not equality). Introduce ON as a synonym for WHERE in inner joins. Make the predicate equality on a set of the columns and it's an equijoin. Introduce USING as a shorthand for ON in equijoins. Make that set all the corresponding columns and it's a natural join. Now include all the rows that didn't match and it's a full outer join. Note that WHERE wouldn't work here. Now just the rows on one side or the other and it's a left/right outer join.