Website development and design blog, tutorials and inspiration

Visual Guide to SQL Joins

A Visual Guide to SQL Joins to help decide which to use.

By , 24th February 2009 in SQL

When combining rows from multiple tables in one query, you may need to use the JOIN command. There are a few different types of joins, and the following should help explain the differences between them.

The syntax will vary depending on which database type you are using. For more details, see the documentation for your database, linked below.

In this example, I have a photo gallery database with two tables. One table is for Galleries and the other for Photos.

Gallery Table

idtitle
1Landscape
2Architecture
3Macro
4Wildlife
5Portrait
6Black and White

Photo Table

idtitlegallery_id
1hills.jpg1
2rivers.jpg1
3mountains.jpg1
4cityscape.jpg2
5tiger.jpg4
6elephant.jpg4
7queen.jpg5
8space.jpg99

Notice that in this dataset that there are no photos in the macro or black and white galleries, and that space.jpg is assigned to a gallery that doesn’t exist in the Gallery table.

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

Inner Join

The inner join is the default used when you don’t specify the type of Join. When you do an inner join of two tables, it returns results which are matched in both tables.

  1. SELECT * FROM photos
  2. INNER JOIN galleries
  3. ON photos.gallery = galleries.id;

SQL Inner Join
SQL Inner Join

idnamegalleryidname
1hills.jpg11Landscape
2rivers.jpg11Landscape
3mountains.jpg11Landscape
4cityscape.jpg22Archetecture
5tiger.jpg44Wildlife
6elephant.jpg44Wildlife
7queen.jpg55Portrait

Full Outer Join

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

  1. SELECT * FROM photos
  2. FULL OUTER JOIN galleries
  3. ON photos.id = galleries.id

SQL Full Outer Join
SQL Full Outer Join

idgalleryidnamename
111Landscapehills.jpg
211Landscaperivers.jpg
311Landscapemountains.jpg
422Architecturecityscape.jpg
544Wildlifetiger.jpg
644Wildlifeelephant.jpg
755Portraitqueen.jpg
899NULLNULLspace.jpg
NULLNULL3MacroNULL
NULLNULL6Black and WhiteNULL

Left Outer Join

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

  1. SELECT * FROM photos
  2. LEFT OUTER JOIN galleries
  3. ON photos.gallery = galleries.id
  4. WHERE galleries.id IS NULL

SQL Left Outer Join
SQL Left Outer Join

galleryidnamenameid
99NULLNULLspace.jpg8

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

  1. SELECT * FROM photos
  2. FULL OUTER JOIN galleries
  3. ON photos.gallery = galleries.id
  4. WHERE galleries.id IS NULL

SQL Left Outer Join
SQL Left Outer Join

galleryidnamenameid
11Landscapehills.jpg
11Landscaperiver.jpg2
11Landscapemountains.jpg3
22Architecturecityscape.jpg4
44Wildlifetiger.jpg5
44Wildlifeelephant.jpg6
55Portraitqueen.jpg7
99NULLNULLspace.jpg8

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

  1. SELECT * FROM photos
  2. FULL OUTER JOIN galleries
  3. ON photos.id = galleries.id
  4. WHERE photos.gallery IS NULL
  5. OR galleries.id IS NULL

SQL Joins
SQL Joins

galleryidnamenameid
11Landscapehills.jpg
11Landscaperiver.jpg2
11Landscapemountains.jpg3
22Architecturecityscape.jpg4
44Wildlifetiger.jpg5
44Wildlifeelephant.jpg6
55Portraitqueen.jpg7
99NULLNULLspace.jpg8
NULL3MacroNULLNULL
NULL6Black and WhiteNULLNULL
Comments

There are no comments for this post. Be the first!

Leave a Reply

Your email address will not be published.