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.
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.