Click here to Skip to main content
11,705,753 members (47,360 online)
Click here to Skip to main content

Visual Representation of SQL Joins

, 3 Feb 2009 CPOL 2.6M 6.2K 438
Rate this:
Please Sign up or sign in to vote.
This article describes SQL Joins in a visual manner, and also the most efficient way to write the visualized Joins.

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:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. OUTER JOIN
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. 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

INNER_JOIN.png

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:

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

Left JOIN

LEFT_JOIN.png

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
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

RIGHT_JOIN.png

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:

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

Outer JOIN

FULL_OUTER_JOIN.png

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:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding JOIN

LEFT_EXCLUDING_JOIN.png

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN

RIGHT_EXCLUDING_JOIN.png

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

OUTER_EXCLUDING_JOIN.png

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:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Examples

Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:

TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

The results of the seven Joins are shown below:

-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)
-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)
-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)
-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)
-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)
-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)
-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)

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.

History

  • Initial release -- 02/03/2009.
  • Version 1.0 -- 02/04/2009 -- Fixed cheat sheet and minor typos.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

C.L. Moffatt
Green Sand Software
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
0x01AA22-Aug-15 5:09
member0x01AA22-Aug-15 5:09 
GeneralMy vote of 5 Pin
0x01AA22-Aug-15 4:55
member0x01AA22-Aug-15 4:55 
GeneralThanks. Pin
Gameslady25-Jul-15 3:31
memberGameslady25-Jul-15 3:31 
QuestionHow would we do two joins (on different variables)? Pin
Rumah Dijual11-Jan-15 4:23
memberRumah Dijual11-Jan-15 4:23 
AnswerRe: How would we do two joins (on different variables)? Pin
C.L. Moffatt23-Jan-15 14:23
memberC.L. Moffatt23-Jan-15 14:23 
GeneralThanks Pin
Member 985941425-Dec-14 18:15
memberMember 985941425-Dec-14 18:15 
QuestionCredit notice on stackoverflow Pin
daantimmer22-Dec-14 1:04
memberdaantimmer22-Dec-14 1:04 
AnswerRe: Credit notice on stackoverflow Pin
C.L. Moffatt23-Jan-15 14:24
memberC.L. Moffatt23-Jan-15 14:24 
GeneralGreat article! Pin
dgirard16-Oct-14 2:22
memberdgirard16-Oct-14 2:22 
QuestionExplanation Pin
spgun15-Oct-14 0:39
memberspgun15-Oct-14 0:39 
AnswerRe: Explanation Pin
C.L. Moffatt23-Jan-15 14:24
memberC.L. Moffatt23-Jan-15 14:24 
GeneralMy vote of 5 Pin
Member 111366167-Oct-14 16:32
memberMember 111366167-Oct-14 16:32 
Newsvisual representation of sql joins Pin
pai100925-Sep-14 22:32
memberpai100925-Sep-14 22:32 
GeneralMy vote of 5 Pin
Sibeesh KV19-Sep-14 19:44
professionalSibeesh KV19-Sep-14 19:44 
GeneralMy vote of 5 Pin
Sibeesh Venu1-Aug-14 6:17
professionalSibeesh Venu1-Aug-14 6:17 
GeneralMy vote of 5 Pin
Shmuel Zang22-Jul-14 18:23
memberShmuel Zang22-Jul-14 18:23 
GeneralThnak you so much...such a nice article Pin
Member 1067882518-Jul-14 9:08
memberMember 1067882518-Jul-14 9:08 
GeneralMy vote of 1 Pin
grenac16-Jul-14 7:20
membergrenac16-Jul-14 7:20 
QuestionThanks Pin
_shivu4-Jul-14 19:32
member_shivu4-Jul-14 19:32 
GeneralMy vote of 5 Pin
Member 1091198128-Jun-14 16:19
memberMember 1091198128-Jun-14 16:19 
QuestionAbout SQL Server Joins tutorials Pin
vandykish16-May-14 6:37
membervandykish16-May-14 6:37 
GeneralJoins Made EASY by C.L.Moffatt Pin
Bala-Seequel26-Mar-14 19:21
memberBala-Seequel26-Mar-14 19:21 
Questionjoins Pin
Member 1067077314-Mar-14 6:54
memberMember 1067077314-Mar-14 6:54 
Questiongreat post ! Pin
Member 1062222624-Feb-14 11:09
memberMember 1062222624-Feb-14 11:09 
QuestionThank you... It was very Helpful Pin
Renuka Rane28-Jan-14 17:56
memberRenuka Rane28-Jan-14 17:56 
QuestionOuter Excluding JOIN - I just needed this Pin
GuyThiebaut26-Jan-14 23:10
memberGuyThiebaut26-Jan-14 23:10 
GeneralMy vote of 5 Pin
mielniczuk20-Jan-14 7:53
membermielniczuk20-Jan-14 7:53 
QuestionNice Explanation :-) Easily understood the concepts Pin
rajas24066-Jan-14 19:54
grouprajas24066-Jan-14 19:54 
QuestionMaybe not a JOIN question... Pin
AlexEvans26-Sep-13 0:01
memberAlexEvans26-Sep-13 0:01 
QuestionVote of 5 Pin
TrollTier23-Sep-13 4:08
memberTrollTier23-Sep-13 4:08 
QuestionHow is join different from regular select ~ where ~ ? Pin
Member 102627609-Sep-13 9:32
memberMember 102627609-Sep-13 9:32 
AnswerRe: How is join different from regular select ~ where ~ ? Pin
C.L. Moffatt10-Sep-13 8:15
memberC.L. Moffatt10-Sep-13 8:15 
GeneralMy vote of 5 Pin
colin harris16-Aug-13 0:48
membercolin harris16-Aug-13 0:48 
GeneralMy vote of 5 Pin
oustaz22-Jul-13 16:55
memberoustaz22-Jul-13 16:55 
GeneralMy Vote of 5 Pin
Anoop Kr Sharma22-Jul-13 16:14
professionalAnoop Kr Sharma22-Jul-13 16:14 
QuestionJOINS Pin
Newbies100211-Jun-13 10:16
memberNewbies100211-Jun-13 10:16 
GeneralMy vote of 5 Pin
Arlen Navasartian30-May-13 5:10
memberArlen Navasartian30-May-13 5:10 
GeneralMy vote of 5 Pin
Maciej Los5-Apr-13 2:39
mvpMaciej Los5-Apr-13 2:39 
GeneralMy vote of 5 Pin
cycnus31-Mar-13 14:53
membercycnus31-Mar-13 14:53 
Questionthank u Pin
Amra Rama12-Mar-13 5:22
memberAmra Rama12-Mar-13 5:22 
GeneralMy vote of 5 Pin
Chathura Roshan22-Jan-13 22:15
memberChathura Roshan22-Jan-13 22:15 
GeneralMy vote of 5 Pin
AndrehPoffo23-Nov-12 5:59
memberAndrehPoffo23-Nov-12 5:59 
QuestionJOIN Pin
mitu sinha13-Nov-12 21:59
membermitu sinha13-Nov-12 21:59 
AnswerRe: JOIN Pin
C.L. Moffatt14-Nov-12 4:04
memberC.L. Moffatt14-Nov-12 4:04 
QuestionLeft Join Pin
mah22446-Nov-12 2:59
membermah22446-Nov-12 2:59 
AnswerRe: Left Join Pin
C.L. Moffatt14-Nov-12 3:59
memberC.L. Moffatt14-Nov-12 3:59 
GeneralMy vote of 5 Pin
kamalsamant25-Oct-12 21:37
memberkamalsamant25-Oct-12 21:37 
GeneralMy vote of 5 Pin
Farhan Ghumra21-Sep-12 0:14
memberFarhan Ghumra21-Sep-12 0:14 
GeneralMy vote of 5 Pin
wrecklass9-Sep-12 13:21
memberwrecklass9-Sep-12 13:21 
GeneralMy vote of 5 Pin
MANISH RASTOGI11-Jun-12 2:30
memberMANISH RASTOGI11-Jun-12 2:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 4 Feb 2009
Article Copyright 2009 by C.L. Moffatt
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid