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 My SQL. This had lead to lots of confusion over time, and this is the best way that I have seen to describe them.
If you are looking for the SQL Server version of this poster, click here.
Download JoinTypes.PDF
Download JoinTypes.PDFIf there is anything missing, please post a comment, there is room to add more to this poster.
Enjoy,
-Steve Stedman
Here is some of the feedback that I have received:
- This is one of the better and more complete posters out there. nice job! Having the code example is really useful too.
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!
Hi. I have a number of databases that are in syntax that I absolutely have no idea how to read. They were text messages that I had deleted from my iphone and when I backed my iphone up, a lot of them somehow are now in syntax. There are a lot of texts I want to retrieve but don’t know how. Would you be able to help me?
There is also another MySQL join type that wasn’t mentioned in the above image. This type is called Union. This one is used to unite columns in different tables under a single column.
Thanks. next time I update the MySQL join types poster I will include the UNION.
Hi
First of all thanks a lot for your work, your poster helps very much.
Yesterday I’ve used it to explain to the students about MySQL JOINs, and I’ve found an error in it:
FULL OUTER JOIN with exclusion has next WHERE conditions:
…
WHERE t2.id IS NOT NULL
…
WHERE t1.id IS NOT NULL
Your code gives simple INNER JOIN result.
The right code is:
…
WHERE t2.id IS NULL
…
WHERE t1.id IS NULL
Hi Steve – I just want to add to Igor’s post – the “FULL OUTER JOIN with exclusion” is indeed correct as he points out.
I meant “incorrect” !
Thanks for the details. I will update this at some point.
Pls Fix download url for MySQL Joins
When you sign up for the SQL Server version you also get the MySQL version. I updated the link so you should be able to download it now.