Joins
Overview
Joins are SQL clauses that combine data from two tables. There are 4 primary types of SQL joins: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
When talking about an SQL JOIN statement, sometimes the first table in the SQL statement is referred to as the "left" table, and the second table is referred to as the
"right" table. For instance, in the following query, A is the left table and B is the right table.
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;While there can be cases where using RIGHT JOIN and FULL JOIN can make your SQL statement more concise, both RIGHT JOIN and FULL JOIN are redundant and can be fully emulated using LEFT JOIN and UNION ALL clauses.
For the purposes of illustration, we will be using a common example of a database for an online store. This online store has two primary tables, orders and customers, shown below. You can download a copy of this SQLite database here.
| id | description | customer_id | value |
|---|---|---|---|
1 |
Water bottle |
1 |
15.00 |
misc |
2 |
Key chain |
1 |
7.50 |
misc |
3 |
Computer |
3 |
2000.00 |
electronics |
4 |
Thumb drive |
3 |
25.00 |
electronics |
5 |
Notebook |
4 |
9.00 |
office |
6 |
Shampoo |
|
5.00 |
beauty products |
7 |
Paper |
| id | first_name | last_name | |
|---|---|---|---|
1 |
Natalie |
Wright |
|
2 |
Ana |
Sousa |
|
3 |
Ben |
Schwartz |
|
4 |
Chen |
Xi |
|
5 |
Frank |
Zhang |
|
6 |
Tianchi |
Liu |
|
7 |
Jake |
Jons |
INNER JOIN
An INNER JOIN, often referred to as simply JOIN, returns rows/records where there is a match in the right table from the left table. Records from the left table that don’t have a match in the right table are excluded. Records from the right table that don’t have a match in the left table are also excluded.
This is appropriate any time you need data from two separate tables, but only when the two tables have something in common. For example, what if our online company decided it wanted to query the database to send an email of appreciation for all customers who have placed at least 1 order. In this case, we want only the emails of those who appear in both the customers and orders table.
SELECT
customers.email
FROM
orders
INNER JOIN customers ON orders.customer_id = customers.id;Which would result in the following table.
LEFT OUTER JOIN
A LEFT OUTER JOIN, often referred to as simply a LEFT JOIN, returns rows/records where every value in the left table is present in addition to additional data from the right table, when there exists a match in the right table.
This is appropriate any time you want all of the data from the left table, and any extra data from the right table if there happens to be a match. For example, what if our online company wanted a list of all orders placed, and if the order wasn’t placed from a guest account, send an email to the customer thanking them for their purchase? In this case, it would make sense to append email information to the order when there is a match.
SELECT
orders.description,
orders.value,
customers.email
FROM
orders
LEFT JOIN customers ON order.customer_id = customers.id;Which would result in the following table, enabling the employee to see orders as well as send out thank you emails.
| description | value | first_name | last_name | |
|---|---|---|---|---|
Water bottle |
15.00 |
Natalie |
Wright |
|
Key chain |
7.50 |
Natalie |
Wright |
|
Computer |
2000.00 |
Ben |
Schwartz |
|
Thumb drive |
25.00 |
Ben |
Schwartz |
|
Notebook |
9.00 |
Chen |
Xi |
|
Shampoo |
5.00 |
|||
Paper |
4.00 |
Had we instead used an INNER JOIN, our list would be missing critical order information.
SELECT
orders.description,
orders.value,
customers.email
FROM
orders
INNER JOIN customers ON order.customer_id = customers.id;| description | value | first_name | last_name |
|---|---|---|---|
Water bottle |
15.00 |
Natalie |
|
Wright |
Key chain |
7.50 |
|
Natalie |
Wright |
Computer |
|
2000.00 |
Ben |
Schwartz |
|
Thumb drive |
25.00 |
Ben |
Schwartz |
Notebook |
9.00 |
Chen |