I'm currently refreshing my knowledge of SQL and am starting with inner and outer joins. With any type of join, there has to be a relationship between two or more tables. This is done with a foreign key, which connects data from one table to some data from another table. A join then returns the shared data from both tables.
I'll go over the different types of joins throughout this blog post, but first, let's view the tables we'll be using as examples. This is an example from W3Schools. Check out their tutorials here.
Table 1: Customers
| id | first_name | last_name |
|---|---|---|
| 1 | Ana | Trujillo |
| 2 | Antonio | Moreno |
| 3 | Ernst | Handel |
Table 2: Orders
| id | customer_id | order_date |
|---|---|---|
| 1 | 1 | 05-19-2021 |
| 2 | 2 | 05-20-2021 |
| 3 | 05-21-2021 |
Inner Join
Our first example is an inner join. This type of join will compare some tables using a foreign key, as mentioned earlier, and return only the matching info from the queried tables. In this case, the foreign key that connects our orders table to the customers table is the customer ID.
We could write our join clause like so:
SELECT Customer.first_name AS "customer_first_name", Customer.last_name AS "customer_last_name", Order.order_date
FROM Customers
INNER JOIN Orders
ON Customers.id=Orders.customer_id
This would return:
| customer_first_name | customer_last_name | order_date |
|---|---|---|
| Ana | Trujillo | 05-19-2021 |
| Antonio | Moreno | 05-20-2021 |
Notice how I renamed some of my columns. This is called an alias. We can do this to better keep track of which table each column is from.
Outer Join
An outer join returns what an inner join would, but also includes rows without matching info from the other table. There are three different types of outer joins.
Left Outer Join
The left join returns all the data in the left-most table (or table 1), plus the shared data from table 2. If any info returned by table 1 is not in table 2, it will return NULL instead.
SELECT Customer.first_name AS "customer_first_name", Customer.last_name AS "customer_last_name", Order.order_date
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.id=Orders.customer_id
| customer_first_name | customer_last_name | order_date |
|---|---|---|
| Ana | Trujillo | 05-19-2021 |
| Antonio | Moreno | 05-20-2021 |
| Ernst | Handel | NULL |
Right Outer Join
The right join does what the left outer join does. Just flipped. It returns all info from table 2 (or the table to the right), plus the shared data from table 1.
SELECT Customer.first_name AS "customer_first_name", Customer.last_name AS "customer_last_name", Order.order_date
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.id=Orders.customer_id
| customer_first_name | customer_last_name | order_date |
|---|---|---|
| Ana | Trujillo | 05-19-2021 |
| Antonio | Moreno | 05-20-2021 |
| NULL | NULL | 05-21-2021 |
Full Outer Join
The full join will return all info from both tables. It's essentially reproducing all info, but in one table instead of two. Again, if any info isn't shared by the other table, NULL will be returned.
SELECT Customer.first_name AS "customer_first_name", Customer.last_name AS "customer_last_name", Order.order_date
FROM Customers
FULL OUTER JOIN Orders
ON Customers.id=Orders.customer_id
| customer_first_name | customer_last_name | order_date |
|---|---|---|
| Ana | Trujillo | 05-19-2021 |
| Antonio | Moreno | 05-20-2021 |
| Ernst | Handel | NULL |
| NULL | NULL | 05-21-2021 |
Where do we go from here?
Using a join for two tables is as simple as it gets. Once we get used to it, we can start joining more than two tables! We might have situations where we want to relate two tables to each other with a third table. For example, we could have a table called 'Kittens', a table called 'Owners' and another table called 'Adoptions' to keep track of owners and which kittens they adopt. This is called a 'many-to-many relationship', whereas the customers and orders example throughout this blog is an example of a 'one-to-many relationship'.
Why use join queries then?
Join queries are a way to retrieve certain information from related tables. From the kitten adoption example, our 'Adoptions' table only holds the owner ID and the kitten ID. We'd use a join query to be able to view more information about the owners and the kittens.
Hope this was helpful! It was certainly a good review for me.
Resources:
Top comments (2)
Fantastic examples and you kept it short. I was just needing a quick explanation of simple joins. Thanks for writing this up.
Thank you! Glad you found it helpful :)