DEV Community

loading...
Cover image for Inner and Outer Joins in SQL

Inner and Outer Joins in SQL

Kirsty Brewster
Software Engineer // I like making cool things with cool people.
・3 min read

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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:

  1. Inner Join vs Outer Join
  2. SQL Joins - W3 Schools
  3. SQL Joins - Learn.co

Discussion (2)

Collapse
raddevus profile image
raddevus

Fantastic examples and you kept it short. I was just needing a quick explanation of simple joins. Thanks for writing this up.

Collapse
kirstybrews profile image
Kirsty Brewster Author

Thank you! Glad you found it helpful :)