TSQL JOIN Types Poster (Version 3)
NOTE:: There is an updated version of the poster available now. Version 4 is now available.
So many times I have been asked for help with a query, where the question really comes down to the understanding of the difference between INNER and LEFT or RIGHT JOINs.
I created this poster a few years ago and I keep it posted on the wall at the office. This way when I am trying to explain JOIN types, I just refer to the poster.
I have created the poster below to help describe JOIN types in SQL Server. This had lead to lots of confusion over time, and this is the best way that I have seen to describe them.
If you are looking for the MySQL version of the poster, click here.
Click to Download TsqlJoinTypes.pdf
The diagram shows the following: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, SEMI JOIN, ANTI SEMI JOIN, LEFT OUTER JOIN with exclusion, RIGHT OUTER JOIN with exclusion, FULL OUTER JOIN, CROSS JOIN, FULL OUTER JOIN with exclusion, CROSS APPLY, OUTER APPLY, two INNER JOINs, two FULL OUTER JOINs, INNER JOIN and a LEFT OUTER JOIN and two LEFT OUTER JOINs.
If you are interested in printing out the poster, download the TsqlJoinTypes.pdf file and print it.
See Also:
- T-SQL INNER JOIN
- TSQL LEFT OUTER JOIN and RIGHT OUTER JOIN
- TSQL FULL OUTER JOIN vs CROSS JOIN
- MySQL JOIN Poster
Comes Complete with:
• Easy to understand lectures with examples
• Assignments
• Sample code to work with
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Very good as a quick reference for beginners.
The right-most Venn for Semi Join needs to say “Table2”.
Maybe a Union and Union All would be interesting to see.
The venn diagram for “two inner joins” has a typo, I think. One of the “Table1″s should be a “Table2”.
Nice document. I can show this to my app developers to teach them. But maybe it should contain a warning that a cross join is not the same as a full outer join, although it looks that way based on the image.
Great chart, Steve. I love it! Not to put more work on you, but would you consider adding the set operators like EXCEPT / MINUS, INTERSECT, and UNION?
Kevin –
Absolutely, that is a great suggestion. Yes, I will do that. Give me a couple days to get around to it. I will let you know when I get to it. Probably sometime early next week.
-Steve
Kevin – Based on your suggestion, I have updated the poster. Version 4 is now available.
http://stevestedman.com/2015/05/tsql-join-types-poster-version-4/
-Steve Stedman
This is a great poster! The visuals included with each join type are really helpful.