SQL Server Join Types Poster

Download PDF

This has been replaced with a new updated version of the poster. Click here to visit the new page.

 

So many times I have been asked for help with a query, where the questoin 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.

 
This has been replaced with a new updated version of the poster. Click here to visit the new page.

 

The diagram shows the following: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN with exclusion, RIGHT OUTER JOIN with exclusion, FULL OUTER JOIN, CROSS JOIN, FULL OUTER JOIN with exclusion, two INNER JOINs, two FULL OUTER JOINs, and two LEFT OUTER JOINs.

 

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.

 

Tagged with: ,
4 comments on “SQL Server Join Types Poster
  1. David says:

    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.

  2. Terrence says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.