Learning never exhausts the mind

By

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.</p>
SELECT * FROM photos
INNER JOIN galleries
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.</p>
SELECT * FROM photos
FULL OUTER JOIN galleries
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.</p>
SELECT * FROM photos
LEFT OUTER JOIN galleries
ON photos.gallery = galleries.id
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.</p>
SELECT * FROM photos
FULL OUTER JOIN galleries
ON photos.gallery = galleries.id
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.</p>
SELECT * FROM photos
FULL OUTER JOIN galleries
ON photos.id = galleries.id
WHERE photos.gallery IS null
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

 

Did you enjoy this post? Let others know!

If you enjoyed this post, please share it with others. Click one of the social media buttons below to share on Facebook, Twitter, LinkedIn, Pinterest or email to a friend.

Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.