MySQL JOIN Types Poster

Download PDF

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.PDFMySqlJoinTypesThumbnailDownload JoinTypes.PDF
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,  FULL OUTER JOIN with exclusion, two INNER JOINs, two FULL OUTER JOINs, and two LEFT OUTER JOINs.

If 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:

SteveStedman5
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!

9 Comments on “MySQL JOIN Types Poster

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

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

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

Leave a Reply

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

*