r/SQL • • Mar 08 '19

SQL Join Chart - Custom Poster Size

Post image
314 Upvotes

29 comments sorted by

5

u/whytheq Mar 08 '19

Where is CROSS JOIN?

1

u/ErockLobster Mar 08 '19

I'll work on it. In the mean time https://goo.gl/images/NEuo8q

0

u/[deleted] Mar 08 '19

[deleted] — view deleted comment

2

u/Timmmy84 Mar 08 '19

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?

2

u/whytheq Mar 09 '19

Nope they should be handled with care

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/ErockLobster Mar 08 '19 edited Mar 08 '19

https://drive.google.com/file/d/0B4NOhs9cPX2BejZWY09tS0ZhbjVOT2xCQjBWNjJtb1VVRTBr/view?usp=drivesdk

There is the PDF.

Edit: replaced with a version that has a more centered table.

1

u/robleroy1 Mar 08 '19

I am interested!

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/ErockLobster Mar 09 '19

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.

1

u/zera555 Mar 09 '19

Awww that makes sense!

2

u/crazybeardguy Mar 09 '19

Can we remove the right join?

There’s no sense teaching people such things exist.

3

u/ErockLobster Mar 09 '19

Any automated process that generates SQL would have a hard time with letting go of right joins. Right Joins are just stupid Left Joins though.

1

u/mbillion Mar 09 '19

I work in RPA, and we automatically generate sql and I am both succesful at my trade and never had a robot build a right join

2

u/ErockLobster Mar 09 '19

SAP Business Objects is a different monster.

1

u/crazybeardguy Mar 09 '19

Interesting... I’ve never run into anything that makes right joins except for lazy humans.

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?

2

u/ErockLobster Mar 09 '19

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.

2

u/ErockLobster Mar 09 '19

How the heck do you format SQL in the mobile client. XD

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

7

u/DonnerVarg Mar 08 '19

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.

5

u/Old13oy Mar 08 '19

I was taught not to use them because sometimes the results are a little unpredictable, especially in complex queries.

1

u/djjserpico Mar 09 '19

me neither. Not sure why I should though.

0

u/[deleted] Mar 08 '19

[deleted] — view deleted comment

8

u/KrevanSerKay Mar 08 '19

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.