In a relational database, all information should only be present once. But you might have information that's separated into different tables that's related to each other.
And you might want to put this related information together to analyse its data – that is, you might want to join all the data (or some of it) together. In this case, you'll need to use SQL's
JOIN statement. Let's learn how it works.
What is a JOIN in SQL?
The JOIN operator lets you combine related information in various ways, as I explained briefly above. There are various types of joins, divided into two main categories – INNER joins and OUTER joins.
The biggest difference between an INNER JOIN and an OUTER JOIN is that the inner join will keep only the information from both tables that's related to each other (in the resulting table). An Outer Join, on the other hand, will also keep information that is not related to the other table in the resulting table.
Let's see how INNER JOINs and OUTER JOINs work in detail to understand them better.
How to Use an INNER JOIN in SQL
The inner join will keep only the information from the two joined tables that is related. If you imagine the two tables as a Venn diagram, the table resulting from an INNER JOIN will be the green highlighted part below where they overlap:
Here's the syntax for an inner join:
We'll see how this works below with an example.
How to Use an OUTER JOIN in SQL
If you want to keep all the data, and not just the data related to each other, you can use an OUTER join.
There are three types of Outer Join:
RIGHT JOIN, and
FULL JOIN. The differences between them involve which unrelated data they keep – it can be from the first table, from the second, or from both of them. The cells without data to fill will have a value of
LEFT JOIN is the mostly universally implemented in all versions of SQL. But this is not the case for
RIGHT JOIN and
FULL JOIN, which are not implemented in various SQL versions.
Let's see how each one works individually. Then we'll see how they all work with examples below.
LEFT OUTER JOIN in SQL
The LEFT OUTER JOIN, or simply Left Join, will keep the unrelated data from the left (the first) table.
You can imagine it with a Venn diagram with two circles, with the resulting table being the green highlighted part which includes both the common/overlapping part, and the rest of the left circle.
The syntax looks like the below. You'll see that it is similar to the Inner Join syntax, but with the
LEFT keyword added.
RIGHT OUTER JOIN in SQL
The RIGHT OUTER JOIN, or simply Right Join, will keep the data in the second table that's not related to the first table.
You can imagine it with a Venn diagram with two circles, with the resulting table being the green highlighted part which includes both the overlapping part, and the rest of the right circle.
The syntax is as below, the only difference is the
FULL OUTER JOIN in SQL
You can think of the FULL OUTER JOIN as the combination of a Left Join and Right Join. It will keep all rows from both tables, and the missing data will be filled in with
You can imagine it with a Venn diagram with two circles, with the resulting table being the green highlighted part which includes everything: the overlapping part, the left circle, and the right circle.
The syntax is as below, using the
Examples of SQL JOIN operator
A possible database for a vet clinic could have one table for pets and one for the owners. Since an owner could have multiple pets, the pets table will have an
owner_id column that points to the owners table.
You could use simple query to get a table with the pet name and the owner name next to each other. Let's do it with all the different JOIN operators.
SQL INNER JOIN example
Let's do it first using
In this case you would
SELECT the column
name from the
pets table (and rename it
pet_name). Then you would select the
name column from the
owners table, and rename it
owner. That would look like this:
SELECT pets.name AS pet_name, owners.name AS owner.
You would use
FROM to say that the columns are from the
pets table, and
JOIN to say that you want to join it with the
owners table, using this syntax:
FROM pets JOIN owner.
And finally you would say that you want to join two rows together when the
owner_id column in the
pets table is equal to the
id column in the
owner table with
ON pets.owner_id = owners.id.
Here it is all together:
SELECT pets.name AS pet_name, owners.name AS owner FROM pets JOIN owners ON pets.owner_id = owners.id;
You would get a table as below, where only the pets connected to an owner and the owners connected to a pet are included.
SQL LEFT JOIN example
Let's do the same query using
LEFT JOIN so you can see the difference. The query is the same other than adding the
SELECT pets.name AS pet_name, owners.name AS owner FROM pets LEFT JOIN owners ON pets.owner_id = owners.id;
In this case the rows from the left table,
pets, are all kept, and when there is data missing coming from the
owners table, it is filled with
It seems there is a pet that is not registered with an owner.
SQL RIGHT JOIN example
If you do the same query using
RIGHT JOIN you would get yet a different result.
SELECT pets.name AS pet_name, owners.name AS owner FROM pets RIGHT JOIN owners ON pets.owner_id = owners.id;
In this case all the rows from the right table,
owners, are kept, and if there is a missing value, it is filled with
It seems there is an owner that does not have a pet registered.
SQL FULL JOIN example
You could do the same query again, using
SELECT pets.name AS pet_name, owners.name AS owner FROM pets FULL JOIN owners ON pets.owner_id = owners.id;
The resulting table is again different – in this instance all rows from the two tables are kept.
It seems that there is a pet without an owner and an owner without a pet in our database.
In a relational database, all data should be written only once. To then analyse this data you need something to join the related data together.
In this article, you have learned how to use the JOIN operator to do so. I hope it will be useful for you, have fun!