You’re probably already familiar with simple SQL queries, such as
“SELECT * FROM table”. Now you are wondering what to do when you have multiple tables and you want to join them. Exactly!
JOIN is the key. In this tutorial, you will learn how to connect data from multiple tables using SQL
What are SQL JOINs?
Databases usually have more than one table.
JOINs are an SQL construction used to join data from two or more tables. When you want to use columns from two tables in a result table, the easiest way to do it is to write a
The syntax of an SQL
SELECT * FROM table1 JOIN table2 ON table1.id1=table2.id2
Let’s go over the elements individually.
FROM keyword, you write the name of the first table that you want to take columns from. Then, you use the
JOIN keyword, followed by the name of the second table that you want to use.
ON statement tells the query on which columns data should be joined. (Usually this is an
ID). In the above syntax, we join the tables using
table2. This is written as
table2.id2 – after the table name there is a dot and then the column name.
It is worth clarifying that the columns on which we join the data are not always in the form of an
ID. They also don’t need to have the same name. For example, it is possible to join two tables based on keys called
cities.zip_code. As you can see, this is not an
ID and the column names for postal codes are different too. However, both columns contain postal codes; two matching fields are needed to make a
Let’s see how SQL JOINs work in a simple example. Suppose we want to find out which artist recorded a certain song. In this basic data model, there are only two tables:
song table contains the columns
artist table consists of
artist_name. As we can see,
artist_id is in both tables; we can use it to join them.
JOIN (also called an
INNER JOIN) takes the left table and matches information for each row from the right table using
artist_id. Thus, the first song, “Dancing Queen”, has an
artist_id equal to “04”; in the
artist table, “04” is assigned to “ABBA”.
As you can see in the
artist table, there is no
artist_id equal to “06”, which is assigned to the song “Rolling in the Deep”. In the result table, only songs matched with an
artist_name are shown. There also aren’t any Michael Jackson songs in the result table, as there is no such song in the
INNER JOIN selects all rows from both tables as long as there is a match between the columns in both tables.
Here’s the code:
SELECT * FROM song JOIN artist ON song.artist_id=artist.artist_id
The second type of join is the
LEFT JOIN. It always takes the first table (meaning the table just after the
FROM keyword) as a whole (all the records in the first table). It takes only matching records from the second table. If there is no matching information, a
NULL value is returned. This means that there is no information available for that particular
Take another look at the
artist table. There is no
artist_name with an
artist_id equal to “06”. This is why the song “Rolling in the deep” did not occur in the
INNER JOIN query result. While it is listed in the
LEFT JOIN example below, it has
NULL values for
SQL LEFT JOIN statement:
SELECT * FROM song LEFT JOIN artist ON song.artist_id=artist.artist_id
The next type of join is the
RIGHT JOIN. Unlike
LEFT JOIN, it takes all the records from the second table (the one immediately after the
JOIN keyword) and only those records from the first table that have matching
artist_ids. In this case, there are no Michael Jackson songs in the database, so there are
NULLs placed for those
artist_ids. And now “Rolling in the deep” has disappeared, as in the right (
artist) table there is no
artist_id equal to “06”.
Here’s the code:
SELECT * FROM song RIGHT JOIN artist ON song.artist_id=artist.artist_id
The last type of
JOIN is the
FULL JOIN. It is a combination of
LEFT JOIN and
RIGHT JOIN, as it takes all the records from both tables and places
NULL values where the information from the matching table is missing. In the example below, you can see
artist_id equal to “06” (yes, it should be Adele!) and
NULLs for Michael Jackson songs with
artist_id equal to “05”. (Yes, “Billie Jean” exists, but SQL does not know it!)
SELECT * FROM song FULL JOIN artist ON song.artist_id=artist.artist_id
Real Life Example: Stores and Products
Now we will show you how to use SQL JOINs in a real business example. Imagine you run a store and you want to see which products are out of stock. You also want to check if your database manager keeps things in order. Let’s assume the database has only two tables:
As you can see, the first table (
product)has two columns:
product_name. If everything is in order, there should be an ID and a name for all the products we have ever sold. The second table (
stock) represents the current stock we have in the store – e. g. there are seven items for the
product_id value of “100”.
Please take a closer look at
product_id column in both tables. Some identifiers are present in both tables (102, 105, 107); some are only in the left one (101, 103, 106); and some are only in the right one (100, 104). This information will be used in the examples below.
Disregarding Missing Information
When we want to see only the products currently in stock and we know already all their names, we can use an
INNER JOIN statement. It selects only these records with an id present in both tables that we want to join. As a result, we get a list of three product names and their quantities. As you can see, you can use both
INNER JOIN statement to do the same thing.
JOIN is a shorthand for
INNER JOIN. The other
JOINs have to be specified explicitly.
AN INNER JOIN
SELECT product_name, stock.product_id, quantity FROM products INNER JOIN stock ON product.product_id = stock.product_id
Which Products Should Be Ordered?
Suppose we want to check which products are out of stock and need to be reordered. To do that, we will use a
LEFT JOIN to match records from the
product table to the
stock table. We can visualize this as putting the
product table on the left and the
stock table on the right. Then we look for records in both tables that match by
product_id. Records without matching
product_id fields are given a
NULL. (See the result table below.)
A LEFT JOIN
SELECT product_name, stock.product_id, quantity FROM products LEFT JOIN stock ON product.product_id = stock.product_id
So, in the
stock table there are no
product_id values equal to “101”, “103” or “106”. This is why the table displays
NULLs for orange juice, coffee, and cereal. These are the products we need to order.
Do We Know What We Sell?
The role of the database manager is to keep everything in our databases in order. Let’s say we want to verify if he or she is doing a good job. We’ll check if the
product table is being updated with new products. For this, we will use a
RIGHT JOIN takes all the records from the
stock table and puts it to the right (just as the
RIGHT JOIN would suggest). Then, it displays only the
product table records that have a matching
A RIGHT JOIN
SELECT product_name, stock.product_id, quantity FROM products RIGHT JOIN stock ON product.product_id = stock.product_id
Show Everything Available
To get a full picture of the situation, we may want to see all product names and all their quantities in both tables. Then we can see which products need to be reordered and which
product_ids are missing a
product_name. For this,
FULL JOIN comes in handy! It takes all records from the left table (
product) and all records from the right table (
stock) and matches only the records that have the same id (
product_id); it inserts a
NULL on the right if there is a missing
product_name or on the left if there are no items in stock.
SELECT product_name, stock.product_id, quantity FROM products FULL JOIN stock ON product.product_id = stock.product_id
Want to Learn More About SQL JOINs?
I hope this quick SQL JOINs tutorial has helped you understand how to join data from two tables. If you’d like more information, I recommend enrolling in Vertabelo Academy, where you can learn how to use SQL (and more!) to deal with databases. The topics are presented in a simple and very understandable way. Try Vertabelo Academy SQL courses for free today!