Understand SQL JOIN with this simple tutorial and don't fail as I did

This article was originally published at: https://www.blog.duomly.com/sql-join-with-examples-tutorial/

Introduction

I remember when I needed a bit deeper knowledge about SQL that I’ve already had, unfortunately, that time had not.

It was a few years ago, when I wanted to get a cool role, with a nice company and cool salary, it was a simple front-end engineer role, but with knowledge of SQL.
I’ve had some SQL knowledge, at least enough to work with that on a simple level, like doing simple queries.

This time tech-recruiter asked me about SQL Join, to be more precise, about the difference between INNER JOIN, FULL JOIN, LEFT JOIN, and the RIGHT JOIN. I did not know that, and this simple question moved me to a much worse position on the salary negotiation.

I decided to learn about it what I could, and today I will give you some basics to help you have a better position than I’ve had.

If you prefer video, here is the youtube version.

What is SQL Join

SQL Join is a Structured Query Language method to concatenate elements(data) from two different SQL tables.

For example, imagine you sell shoes online and would like to show all products that will fit your customer's feet.

You have two tables in your online store, first is the table "Users" with columns "id", "name", "email", and "size".

We can create it by typing in our SQL editor:

CREATE TABLE Users (
  userId int,
  name varchar(255),
  email varchar(255), 
  footSize int
);

Next, let's add some values into the table:

INSERT INTO Users
VALUES 
  (1, 'Radek', 'radek@radek.com', 12),
  (2, 'Mark', 'mark@mark.com', 9),
  (3, 'Bruce', 'bruce@test.com', 13);

Result:

The second one is a table named "Shoes", with columns "productId", "model", "brand", and "size".

We will create that by typing that code in our SQL editor:

CREATE TABLE Shoes (
  shoeId int,
  model varchar(255),
  brand varchar(255), 
  shoeSize int
);

Next, let's add some products:

INSERT INTO Shoes
VALUES 
  (1, 'Shoe model 1', 'Shoe brand', 12),
  (2, 'Shoe model 2', 'Shoe brand', 9),
  (3, 'Shoe model 3', 'Shoe brand', 12),
  (4, 'Shoe model 4', 'Shoe brand', 8);

Now we would like to see shoes that will fit our user's feet.

To do that we can do join method, like:

SELECT shoeId, model, brand, shoeSize FROM Users user
JOIN Shoes shoe ON user.footSize=shoe.shoeSize
where user.name = 'Radek'

Result:

SQL Join syntax

Basically, SQL Join syntax looks like:

SELECT columns 
FROM firstTable JOIN secondTable
ON columnFromFirstTable = columnFromSecondTable
WHERE condition

Of course, it will be different in every case, but the main template looks similar to that, and you can build your own queries base on this one.

SQL Join types

We have four main types of SQL Join method.

INNER JOIN

The first one is the INNER JOIN method that will pick rows that fit conditions from the first and the second table.

LEFT JOIN

The second one is the LEFT JOIN method that will pick all rows from the first table with the condition-fitting rows from the second table.

RIGHT JOIN

The third one is the RIGHT JOIN method that will pick all rows from the second table with the condition-fitting rows from the first table.

FULL JOIN

The last one is the FULL JOIN method that will pick all rows from the second table and the first table, whether condition-fit or not.

Inner Join example

In this case, we will want to show only shoes that fit our user „Radek".

SELECT shoeId, model, brand, shoeSize FROM Users user
INNER JOIN Shoes shoe ON user.footSize=shoe.shoeSize
where user.name = 'Radek'

Result:

As we can see, DB returned us only items where shoeSize is equal to our user's footSize field.

Left Join example

We can join two tables and show all users without passing the condition if there are any shoes for him or not.

SELECT userId, shoeId, model, brand, shoeSize FROM Users user
LEFT JOIN Shoes shoe ON user.footSize=shoe.shoeSize

Result:

In this case, DB returned us all users with his value, but the values of shoes are null in rows that were wasn't a product for this one customer.

Right Join example

Now, we can show all shoes, and if there won't be any users that we can fit our shoe, it will be just null in the "userId" section.

SELECT userId, shoeId, model, brand, shoeSize FROM users
RIGHT JOIN shoes ON users.footSize=shoes.shoeSize

Result:

Here we can notice DB returned all rows with shoes, but the empty column in the user that wasn't a correct customer for the shoes.

Full Join example

Here we will join both tables, and show all records, without passing any conditions.

SELECT userId, shoeId, model, brand, shoeSize FROM users
FULL JOIN shoes ON users.footSize=shoes.shoeSize

Result:

Here we can see we have all, it doesn't matter if shoes have potential owners or users have the possibility of buy shoes.

Conclusion

I've explained to you a few minor basics about SQL Join method, that can help you with SQL.
Now you get knowledge of how SQL Join works, what are differences between SQL Join types, and when to apply each of them.

I hope you will be able to recognize them when needed, and that will help you to get some benefits from it.

Thanks for reading,
Radek from Duomly - Programming courses online