Introduction

This is just a simple article visually explaining SQL JOINs.

Background

I'm a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOINs, but I couldn't find any to my liking. Some had good diagrams but lacked completeness (they didn't have all the possible JOINs), and some were just plain terrible. So, I decided to create my own and write an article about it.

Using the code

I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:

INNER JOIN

LEFT JOIN

RIGHT JOIN

OUTER JOIN

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

OUTER JOIN EXCLUDING INNER JOIN

For the sake of this article, I'll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN, RIGHT EXCLUDING JOIN, and OUTER EXCLUDING JOIN, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE clause).

Inner JOIN

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

Left JOIN

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

SELECT<select_list>FROM Table_A A
LEFTJOIN Table_B B
ON A.Key = B.Key

Right JOIN

This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

Outer JOIN

This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:

Note on the OUTER JOIN that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that's how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY statement).

You can visit the Wikipedia article for more info here (however, the entry is not graphical).

I've also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.

This is very misleading. The SQL is wrong in most cases, unless you specically want to have the same columns for each set of results. For instance, in practice the top 2 are "SELECT <select_list> FROM TableA A" & "SELECT <select_list> FROM TableB B", respectively. Some for the rest you could use NOT EXISTS (expect of course the case where it is just INNER and FULL OUTER JOIN). Which comes to the nub of the problem, this visual representation is for sets - SQL is based (however loosely) on Relations and not Sets (look for references to E F Codd or books by C J Date). It also at one point seems to imply that there is a "natural ordering" to the results from SQL. That is also very misleading, an ordering can appear to be there (without an ORDER BY), but one tiny change to the optimised execution plan can throw that out - almost seemingly at random. These are all bad things I've had to correct in developed code over many years. It must run to 1000's of statements that I have required change to correct ensuing bugs or perfomance problems.

Thanks for the wonderful visual representation of joins!! I had found a similar one somewhere but this is better. I am looking for SQL Server Joins video tutorials. Can you suggest some to me..I have tried…some like these

As a student it was confusing for me to understand difference between LEFT & RIGHT JOINS.
This article has made my concepts clear about all types of joins.
Again thanks for examples with files and diagrams

Excellent article - I needed the Outer Excluding JOIN and did not know the structure although I knew the visualisation of it.
I needed to compare the contents of two tables to find differences.
The ven diagrams are an excellent addition to this article +5

“That which can be asserted without evidence, can be dismissed without evidence.”

Yes they both give the same result. Neither is faster, but let me explain why. The comma separated table list in the FROM statement with the join occurring in the WHERE clause was the old way of doing joins. The comma separated table list is asking SQL to do a Cartesian join, i.e. every record in one table will be matched with every record in the other table. This can give you a huge result set. I did a Cartesian join on two local tables I have, one with 2,276 rows and another with 1,207 rows. The result set was 2,747,132 rows and it took 36 seconds to run (2,747,132 being the product of 2,276 X 1,207, or like I said one record for every other record). The old way would have to do this first then "filter" those two million plus records to find only the ones where the keys matched. So you would have already started off with a 36 second baseline before it even got to the filtering.

Luckily, modern DataBase Management Systems (DBMS) don't do this. They recognize what it is you are trying to do and convert this:

This is why in a modern DBMS the two queries take the same amount of time. If you look at the execution plans for both queries, you'll see that the DBMS is doing an INNER JOIN for both.

However, you are better off writing the query with the INNER JOIN because more people will understand what it is you are trying to accomplish. It also makes it more clear when you have additional items you want to filter on within the WHERE clause or when you are joining multiple tables.