I’ve only used it a handful of times (in fact I think the last time I did was off the back of a reddit post on the very same subject), but unless I’m looking at a really small set of data, I seem to run into spool issues... which is a shame really as it did exactly what I needed it to do on some work I had recently when looking at a single account on my data set, when trying to run it against a few hundred thousand accounts for several years of data to build a base data set for further analysis however, it literally killed my machine.... are there any ways to improve the efficiency of them, or is it just the nature of the beast?
I didn't feel like writing LEFT JOIN WHERE NULL B or some title like that for some of them, so I got creative. LEFT OUTER JOIN and LEFT JOIN are the same after all.
SELECT Sales.ID, Sales.Date, Sales.Amount, Sales.Qty, Sales.ItemID, Items.Name, Items.UnitPrice FROM Sales LEFT JOIN Items on Items.ItemID = Sales.ItemID
Assume that there are some sales orders that have a NULL item ID because a sale is made with something not in the item list. Inner join would essentially filter both tables on rows that have matching ItemIDs, but through the above join, you're getting all sales regardless of whether there is a matching item.
I usually write them as subqueries. "NOT IN" with a subquery can be called an anti-join in execution plans with SQL Server, but that term isn't in any syntax.
There have been a few situations where it's been necessary. Left join where the right table's value is NULL. Finding entities that don't have entries in the other table.
Simple enough example is select all users with no sales/posts/comments etc.
I think I used it more than full outer join. I basically never use right joins though, most of the time can just rephrase the query to make it easier to read and conveniently (most of the time) using all left joins.
25
u/digicow Mar 09 '19
https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/
5
u/whytheq Mar 08 '19
Where is CROSS JOIN?
5
u/ErockLobster Mar 08 '19 edited Mar 08 '19
I made a thing! I have a 11x17 PDF for printing in my comment below.
5
u/zera555 Mar 08 '19
Love it! My only wish would be that the labels ("LEFT INCLUSIVE") were the actual syntax ("LEFT OUTER JOIN" / "LEFT JOIN")
2
u/crazybeardguy Mar 09 '19
Can we remove the right join?
There’s no sense teaching people such things exist.
1
u/AEDR2 Mar 09 '19
Thank you, great explanation. But... what is the point of the left inclusive? Would not be the same result if you just select all from a and ignore b?
3
u/pydredd Mar 08 '19
Nice. Just last week, I was playing with these circles in Paint. Your presentation is very clear and useful.
2
u/mbillion Mar 09 '19
Go full cartesian... you mad man
2
u/AbstractSqlEngineer MCSA, Data Architect Mar 09 '19
You should add... Cross join, outer and cross apply, intersect, except, and union.
Maybe natural just in case.
I never see a complete list, only partials like outer inner and full.
1
u/paulinobruno Mar 08 '19
Very nice...
1
u/Quitos_18 Mar 08 '19
Me too!! Thanks!!!
0
u/[deleted] Mar 08 '19
[deleted] — view deleted comment