What is all this about `JOIN`??¶

cat database dog

The different kinds of JOIN in SQL reflect different ways of handling mismatches in records between related tables. In order to get the hang of them, let's walk through some examples.

Consider these two tiny (slightly modified from before) tables: originators and words. There's an originator who doesn't have any words (originator 4), and a word that doesn't have any originator (word 4).

id	originator
1	Audra Vincent
2	Michelle Clark
3	Lawrence Nicodemus
4	Amy Fountain

id	root	nicodemus	english	originator_id
1	a	a	hello.	3
2	bc	buts	boots.	3
3	bs	tbilso	dragonfly.	1
4	cč	tsech	thunder.

INNER JOIN¶

The INNER JOIN says to return only the rows from the words table that correspond with a record in the originators table. If a word didn't have a value for originator it would not show up in our query results. Similarly, if there were an originator who didn't have any rows in the words table, that row wouldn't be included. So the results of this query:

SELECT words.root, words.nicodemus, words.english, originators.originator
FROM words 
INNER JOIN originators ON words.originator_id=originators.id;

Is this result set:

root	nicodemus	english	originator
a	a	hello.	Lawrence Nicodemus
bc	buts	boots.	Lawrence Nicodemus
bs	tbilso	dragonfly.	Audra Vincent

LEFT JOIN¶

A LEFT JOIN includes all the records from the first table regardless of whether they have a match in the second, but only the records in the second table that match something in the first. Here's the SQL:

SELECT words.root, words.nicodemus, words.english, originators.originator
FROM words 
LEFT JOIN originators ON words.originator_id=originators.id;

root	nicodemus	english	originator
a	a	hello.	Lawrence Nicodemus
bc	buts	boots.	Lawrence Nicodemus
bs	tbilso	dragonfly.	Audra Vincent
cč	tsech	thunder.

The two join types above are available in our SQLite database, the two below are not. Not all database applications support all of the different possible joins. We figured you might be curious about them, so we include them here as points of information.

RIGHT JOIN (not supported in SQLite)¶

And a RIGHT JOIN that does the opposite - all records in the second table show up whether or not they have a match in the first one, but only rows in the first table that have a match in the second. In applications that support it, this is what the query would look like (in SQLite we'd just name the tables in the other order and do a LEFT JOIN)

SELECT words.root, words.nicodemus, words.english, originators.originator
FROM words 
RIGHT JOIN originators ON words.originator_id=originators.id;

root	nicodemus	english	originator
a	a	hello.	Lawrence Nicodemus
bc	buts	boots.	Lawrence Nicodemus
bs	tbilso	dragonfly.	Audra Vincent
			Amy Fountain

FULL JOIN (not supported in SQLite)¶

There is also an OUTER JOIN that says to return all of the records in the first table (regardless of whether they have a match in the second), and all the words in the second table (regardless of wether they have a match in the first table). The query

SELECT words.root, words.nicodemus, words.english, originators.originator
FROM words 
FULL JOIN originators ON words.originator_id=originators.id;

Would return something like this:

root	nicodemus	english	originator
a	a	hello.	Lawrence Nicodemus
bc	buts	boots.	Lawrence Nicodemus
bs	tbilso	dragonfly.	Audra Vincent
cč	tsech	thunder.
			Amy Fountain

Some learners find venn diagrams like these (thanks to Zahin Rahman at learnSQL.com for this particular one) to be helpful in understanding these different join types:

These are not the only kinds of JOIN statements that SQL provides. There are also outer joins, natural joins, cross joins, even self joins. But this set might be good enough to start, eh?

Make sense? It's OK if not, usually you'll have helpers in your database application so that you don't have to figure these things out on your own.

How'd we do?¶

Do you feel like you understand the different kinds of JOIN in SQL? If so, great! If not, we hope you'll ask more questions.

You can contact John, Gus, or Amy and we'll do the best we can to help!

At this point, you might want to go back to our our Day 2 materials 🚀 or to the CoLang Workshop Homepage 🚀