All About SQL Joins

I was recently asked in an interview to explain the difference between a JOIN and a LEFT JOIN in SQL. Admittedly it had been a while since I had thought about the different SQL joins and I fumbled a little with my answer. So I am taking this opportunity to dive into SQL joins.

What is a SQL join?

A SQL join is a way to combine rows from two or more tables based on a common column between them.

Let’s break that down using a book and author example where authors can have many books, and a book belongs to an author.

The authors table has an id primary key, name, and age:

authors
id   |   name              |   age
1    |   James Patterson   |   72
2    |   Dan Brown         |   55
3    |   JK Rowling        |   54
4    |   David Baldacci    |   59

The books table has an id primary key, title, page_count, and author_id, which is a foreign key referencing the id of an author:

books
id    |   title                    |   pages      |   author_id
1     |   Along Came a Spider      |   435        |   1
2     |   The Da Vinci Code        |   454        |   2
3     |   The Lost Symbol          |   528        |   2
4     |   HP & Sorcerer's Stone    |   309        |   3
5     |   HP & Chamber of Secrets  |   341        |   3

A foreign key is used to form a relationship between two tables, where the foreign key references the primary key of another table. The foreign key is the common column we use to join two tables together.

If we want to query all of the books and include the author information, we need to join the two tables together.

Types of SQL joins

There are four types of SQL joins:

• (INNER) JOIN returns all rows where there is a match in both tables
• LEFT (OUTER) JOIN returns all rows from the left table and the matched rows from the right table
• RIGHT (OUTER) JOIN returns all rows from the right table and the matched rows from the left table
• FULLL (OUTER) JOIN returns all rows where there is a match in one of the tables

The words in parentheses are optional when writing the SQL query. Also note that RIGHT JOIN and FULL JOIN are not supported in SQLite.

A good way to remember this is to think about a Venn diagram:

SQL join diagram from https://www.w3schools.com/sql/sql_join.asp

How to use a SQL join

Back to our book and author example, we can (INNER) JOIN the books and authors tables on the author_id :

SELECT *
FROM authors
JOIN books
ON authors.id = books.author_id

id| name       | age| id| title                  | pages | author_id
1 | JPatterson | 72 | 1 | Along Came a Spider    | 435   | 1
2 | Dan Brown  | 55 | 2 | The Da Vinci Code      | 454   | 2
2 | Dan Brown  | 55 | 3 | The Lost Symbol        | 528   | 2
3 | JK Rowling | 54 | 4 | HP & Sorcerer's Stone  | 309   | 3
3 | JK Rowling | 54 | 5 | HP & Chamber of Secrets| 341   | 3

Notice how the author David Baldacci (id of 4 in the authors table) is not included in our result, because there is not a book with author_id of 4 in the books table. To be included in the results of an (INNER) JOIN, there must be a match in both tables.

If we compare that with a LEFT (OUTER) JOIN:

SELECT *
FROM authors
LEFT JOIN books
ON authors.id = books.author_id
id| name      | age| id | title                  | pages | author_id
1 | JPatterson| 72 | 1  | Along Came a Spider    | 435   | 1
2 | Dan Brown | 55 | 2  | The Da Vinci Code      | 454   | 2
2 | Dan Brown | 55 | 3  | The Lost Symbol        | 528   | 2
3 | JK Rowling| 54 | 4  | HP & Sorcerer's Stone  | 309   | 3
3 | JK Rowling| 54 | 5  | HP & Chamber of Secrets| 341   | 3
4 | DBaldacci | 59 |null| null                   | null  | null

Now the author David Baldacci is included in our result, because it exists in the left table. Our left table here is authors and in a LEFT (OUTER) JOIN we return all rows from the authors table and all rows from the books table with a match in the authors table (i.e. all books with an author_id).

Happy joining!

Sources

SQL Joins Explained

SQL Joins

Side Effects of SQL Joins in One-to-Many Relationships