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.

The EXCEPT and INTERSECT operators will not give you the same results as my examples above. Did you try using the EXCEPT and INTERSECT operators with the data I have given?

The EXCEPT and INTERSECT operators are limiting UNION operators. To use them the SELECT statements must be identical.

The EXCEPT operator would work showing me which keys were in the left table and not in the right table but I could not get any other column information from the left table.

The INTERSECT operator is kind of like an INNER JOIN operator with a DISTINCT operator.

Neither of these will accomplish what I have described in the article above.

Let's use the LEFT EXCLUDING JOIN I have described above.

MY EXAMPLE

SELECT A.PK AS A_PK, A.Value AS A_Value
FROM Table_A A
LEFTJOIN Table_B B
ON A.PK = B.PK
WHERE B.PK ISNULL

YOUR SUGGESTION

SELECT A.PK AS A_PK, A.Value AS A_Value
FROM Table_A A
EXCEPTSELECT B.PK, B.Value
FROM Table_B B

My example will return the expected results (I've added dashes and extra zeros just so the columns will line up).A_PK----A_Value
0004----LINCOLN
0005----ARIZONA
0010----LUCENT

Your example will return something that is not expected (at least it isn't what we want in this case).A_PK----A_Value
0001----FOX
0002----COP
0003----TAXI
0004----LINCOLN
0005----ARIZONA
0006----WASHINGTON
0007----DELL
0010----LUCENT

Your suggestion will yield us a LEFT JOIN because we want to include the VALUE field.

Your suggestion of using the INTERSECT operator will actually yield no results. Run the query below.

SELECT A.PK AS A_PK, A.Value AS A_Value
FROM Table_A A
INTERSECTSELECT B.PK, B.Value
FROM Table_B B

There will be no instances where both the primary keys and the values match (at least using my data examples).

The EXCEPT and INTERSECT operators are useful, but they are not the same as the examples shown above.