TSQL JOIN Types Poster (Version 4)
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. This will help >>> Try our new JOIN Types Course
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.
Version 4 of the poster contains 3 new Venn diagrams, INTERSECT, UNION, and EXCEPT. This caused the poster to need to be split onto 2 pages.
If you are looking for the MySQL version of the poster, click here.
Click to download the TSqlJoinTypePoster.pdfThe 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, two LEFT OUTER JOINs, INTERSECT, UNION, and EXCEPT.
If you are interested in printing out the poster, download the TSqlJoinTypePoster.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!
I was explaining the diagrams to my 5 year old, and she pointed out there are two Table1’s in the Two INNER JOIN section of the TSqlJoinTypePoster.pdf v4.
Thanks for the feedback. I will take a look at the poster and get it fixed.
This is perfect not only visualizing different types of joins but the syntax for creating. Thanks for sharing!
There is a bug in Sample Schema on page 2.
Instead of “Table2.fk_table3 -> Table3.id” it should be “Table1.fk_table3 -> Table3.id”, I guess.
Thanks Pavel, we’ll take a another look at it.
Is the last entry in Table 2 supposed to have an id of 9?
You might think so, but giving it an ID of 1 adds an interesting side effect into the demo I do with this table. If you have 2 colors associated with one ID, it adds to the query complexity. This could of course only happen if there is no real primary key on the ID column. -Steve