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 tablesLEFT (OUTER) JOIN
returns all rows from the left table and the matched rows from the right tableRIGHT (OUTER) JOIN
returns all rows from the right table and the matched rows from the left tableFULLL (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:
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_idid| 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!