After someone reported a small bug in the “two INNER JOINs” diagram, I have created an update to the poster. This is update 4.1, only a minor version upgrade due to only a small change. The thing I love about the SQL Server community is the positive feedback that people give to help improve things.
So many times I’ve been asked for help with a query where the question comes down to 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. Now 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.
- Version 4.1 is just a bug fix on the “two INER JOINs” diagram, and a page added at the end with details on the free Database Health Monitor download.
- Version 21.5 updated a bug on the schema section with the foreign keys not shown correctly.
If you are looking for the MySQL version of the poster, click here.
Click to download the TSqlJoinTypePoster.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, 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!
Thanks Steve, this is a good reference for all devs\qa as well as the DBA :)
That is a great reference. I am printing it out and putting it on my cubeicle wall.
Have you considered adding (or creating a parallel version) showing the implicit join syntax alternatives?
I realise that the join syntax you show is (currently) considered best practice, because of the somewhat arcane syntax of outer joins in TSQL (*= etc…), but many of the sites I have worked at have had so many developers over the years that there is almost no chance of imposing a standard. More ‘old schoolers’ (me included :-). use the ‘where’ clause syntax, to the extent that there are occasionally problems in troubleshooting or modifying code between the 2 approaches. Installed code-bases (think, for eg, of bank End of Day reporting systems, where patching a working report is *never* going to happen, unless changes to the DB cause an extract to fail for some reason). I maintain an incomplete list myself, for showing to people as needed, but I only really focus on left side approaches and reversing table order – which can occasionally cause performance issues.
eg.
SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
vs.
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Great poster by the way – and thanks for keeping it free.
Graeme,
Thanks for the suggestion. I have specifically avoided the older arcane style join syntax. I guess the reason for that is that I don’t want to promote that style.
I know that I used code with that alternative JOIN syntax 15 or more years ago, and I was able to escape it and move the the explicit INNER JOIN, LEFT OUTER JOIN … style format. I guess I don’t want to be responsible for anyone discovering the old style join syntax and adopting it.
Thanks for the feedback, you do have a really good point that there is a great deal of legacy code out there in production systems still using the old style join syntax.
-Steve Stedman.
I think you mean thing!
“The >>think<< I love about the SQL Server community is the positive feedback that people give to help improve things."
Graham – Thanks for catching my typo. It has been corrected.
-Steve Stedman
This is great. I like the way you included the Not Exists and exclusion. I’m wondering though, if there is a way to generically illustrate, how to teach someone what columns to join. I don’t know the answer, I’m just wondering. Further explanation of fk and the id would be needed for a new person.
You really need to understand the data and the foreign key relationship between the tables to know how to join. I have seen problems where people join on the wrong ID because they didn’t understand the key relationships.
Steve,
Thank you for publishing the TSQL JOIN TYPES poster. For years it has been a helpful guide for dev interview questions and to verify the basic join constructs sometimes when my more complicated queries return unexpected results. Such was the case when I wanted a result set with all of the values in Table1 and all related values from Table2 as in a LEFT OUTER JOIN. I was convinced there was a SQL config problem, not my query because my result set did not include rows where Table2 data is NULL.
I eventually read on the innertubes that my WHERE clause was eliminating the rows that returned null values and to proof it, I created the database from your poster.
My plan was to keep this reference database handy for these situations. I added a 3rd column, YearDate to Table2 and added a WHERE clause to the LEFT OUTER JOIN so that my query could filter on YearDate:
SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id WHERE t2.YearDate Table2.id and Table2.fk_table3 -> Table3.id.
Given the column names of the tables, the foreign keys should be: Table1.fk -> Table2.id and Table1.fk_table3 -> Table3.id. Correct?
Just so you know, documentation errata has no effect on your SQL Guru status with my database friends and me. Thanks for sharing your expertise and wisdom.
-mark
Mark,
>>> Given the column names of the tables, the foreign keys should be: Table1.fk -> Table2.id and Table1.fk_table3 -> Table3.id. Correct?
Yes that is correct. Sorry but the nature of how the join types poster evolved, I ended up with some poor naming conventions. Perhaps I will get to updating it one day.
I am glad that you are able to use the poster and that you are enjoying it.
-Steve Stedman
Thanks Steve, very usefull
Hallo MrSteves,
I was searching on the Internet for SQL review and I came to your website.I liked the Joins tables Poster, but it seems that you forget to update the Right Outer Join and it is exactly as Left Outer Join.
Best Regards,
Mohammed
Thanks Steve!
This is really handy and much cleaner than my notes on the topic.
Tim
And this is gonna get me through my interview.
Thanks Steve!
very useful to understand all cases.
I know my question is kind of strange, but, maybe somebody can imagine the inverse result of the Union.
Inverse of ( select fk as id from table1
union
select id from table2 )
Is it something like Infinite?