SQL Server Join Types Poster
This has been replaced with a new updated version of the poster. Click here to visit the new page.
>>> Try our JOIN types course today!
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.
Questions that have come up based on this poster:
Question 1: That’s a cool poster. But you can just use left jobs for everything starting with your main table. Filter out what you don’t want in the where statement or with isnull()
Answer: You are close here., the ISNULL function is used to check if a value is null, and if it is null, then to convert it to something else. Instead you want to use IS NULL (notice the space between them). This is illustrated in the 4th row on the poster for either the LEFT JOIN with exclusions or the RIGHT JOIN with exclusions.
This has been replaced with a new updated version of the poster. Click here to visit the new page.
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!
There appears to be a mistake in the FULL OUTER JOIN with exclusion. Your WHERE clause requires a null in t1 AND t2. This doesn’t really correspond to your diagram. I guess you meant OR instead of AND.
I wonder about the use of the column name fk. Are we supposed to understand that fk is subject to a foreign key constraint that references id in Table1? If so then the only possible rows returned by on the right hand side of your RIGHT JOIN query would be those where fk was null (because non-null values that aren’t in t1 would not be permitted by the FK constraint). Are nulls in the base tables intended to be part of the sets included in the other diagrams as well?
What also isn’t shown by the diagrams is that joins can generate more rows than existed in the underlying tables.
SQL-style joins don’t correspond very well to set theory the relational model is based on set theory but SQL is based on the concept of bags which can include duplicate rows and nulls. I’m not sure that Venn diagrams adequately represent the complexities of joins in SQL.
Thanks for the feedback. I am working on a version 2, update to the chart.
I think there is a mistake in the “FULL OUTER JOIN with exclusion.” I think the AND in the WHERE clause should be an OR otherwise you’ll get always get no rows.
You are indeed correct. I am working on version 2 of the poster now. thanks for catching that.