SQL JOINs for Beginners

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 SQL JOINs tutorial for beginners, you will learn how to connect data from multiple tables.

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 JOIN query.

The syntax of an SQL JOIN is:

SELECT * 
FROM table1
JOIN table2
  ON table1.id1=table2.id2

As this is an SQL JOINs tutorial for beginners, let’s start with the basics. We’ll go over the elements individually.

After the 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.

Next, the 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 id1 from table1 and id2 from table2. This is written as table1.id1 and 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 orders.post_code and 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 an SQL JOIN.

INNER JOIN

Let’s continue with our SQL JOINs for beginners guide. We’ll 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 and artist. The song table contains the columns song_id, song_title and artist_id. The artist table consists of artist_id and artist_name. As we can see, artist_id is in both tables; we can use it to join them.

The simplest 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 song table.

Remember that 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

LEFT JOIN

The second type of join we’re gonna mention in this SQL JOINs tutorial for beginners 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 ID.

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 artist_id and artist_name.

Here’s the SQL LEFT JOIN statement:

SELECT *
FROM song
LEFT JOIN artist
  ON song.artist_id=artist.artist_id

RIGHT JOIN

The next type of join in our SQL JOINs tutorial for beginners 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 song_ids, song_titles and 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

FULL JOIN

The last type of JOIN we’re gonna discuss in our SQL JOINs for beginners guide 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 NULLs for 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. This time, our SQL JOINs tutorial for beginners will use an example from the retail industry. 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: product and stock.

As you can see, the first table (product) has two columns: product_id and 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 already know 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 JOIN and 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.

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 product_id.

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.

FULL JOIN

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?

You can find more examples of using SQL JOINs on our YouTube channel - We Learn SQL. Check it out and remember to subscribe.

I hope this quick SQL JOINs tutorial for beginners has helped you understand how to join data from two tables. If you’d like more information, I recommend enrolling in LearnSQL.com courses, 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 our SQL courses for free today!