Understand SQL JOIN with this simple tutorial and don't fail as I did
Tags: beginners, sql, tutorial, codenewbie
Published: 2020-05-04 15:56:56

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.

{% youtube aRPQFWgWyok %}

###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:
```SQL
CREATE TABLE Users (
  userId int,
  name varchar(255),
  email varchar(255), 
  footSize int
);
```
Next, let's add some values into the table:
```SQL
INSERT INTO Users
VALUES 
  (1, 'Radek', 'radek@radek.com', 12),
  (2, 'Mark', 'mark@mark.com', 9),
  (3, 'Bruce', 'bruce@test.com', 13);
```

Result:

![SQL JOIN users table](https://dev-to-uploads.s3.amazonaws.com/i/zfs7v5fw6sie8vjj8xkp.png)

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:
```SQL
CREATE TABLE Shoes (
  shoeId int,
  model varchar(255),
  brand varchar(255), 
  shoeSize int
);
```
Next, let's add some products:
```SQL
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);
```

![SQL JOIN shoes table](https://dev-to-uploads.s3.amazonaws.com/i/g6rc5eg9uud9j2y1b5f3.png)

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

To do that we can do join method, like:
```SQL
SELECT shoeId, model, brand, shoeSize FROM Users user
JOIN Shoes shoe ON user.footSize=shoe.shoeSize
where user.name = 'Radek'
```
Result:

![SQL JOIN example](https://dev-to-uploads.s3.amazonaws.com/i/oxlo2bzqjlp41asjdxst.png)

###SQL Join syntax###

Basically, SQL Join syntax looks like:
```SQL
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.

![SQL INNER JOIN](https://dev-to-uploads.s3.amazonaws.com/i/8g2dgm77imh0832g5mtw.jpg)


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

![SQL LEFT JOIN](https://dev-to-uploads.s3.amazonaws.com/i/d8c0yvbokx6mc5ltxzgh.jpg)

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

![SQL RIGHT join](https://dev-to-uploads.s3.amazonaws.com/i/nc0h1h0ddsqu0xuh5lcc.jpg)

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

![SQL FULL JOIN](https://dev-to-uploads.s3.amazonaws.com/i/xn6v9p0wreqno306svw3.jpg)



###Inner Join example###
In this case, we will want to show only shoes that fit our user „Radek".
```SQL
SELECT shoeId, model, brand, shoeSize FROM Users user
INNER JOIN Shoes shoe ON user.footSize=shoe.shoeSize
where user.name = 'Radek'
```
Result:

![SQL INNER JOIN example](https://dev-to-uploads.s3.amazonaws.com/i/bw070q79ituxpb5gqbyy.png)

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.
```SQL
SELECT userId, shoeId, model, brand, shoeSize FROM Users user
LEFT JOIN Shoes shoe ON user.footSize=shoe.shoeSize
```


Result:

![SQL LEFT JOIN example](https://dev-to-uploads.s3.amazonaws.com/i/3f61ohhgei23chvoz46u.png)

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.
```SQL
SELECT userId, shoeId, model, brand, shoeSize FROM users
RIGHT JOIN shoes ON users.footSize=shoes.shoeSize
```
Result:

![SQL RIGHT JOIN example](https://dev-to-uploads.s3.amazonaws.com/i/0gdi065q9a20mfzrvd1o.png)

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.
```SQL
SELECT userId, shoeId, model, brand, shoeSize FROM users
FULL JOIN shoes ON users.footSize=shoes.shoeSize
```
Result:

![SQL FULL JOIN example](https://dev-to-uploads.s3.amazonaws.com/i/dt6gklxeshn9k7dauzwd.png)

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.

<a href="https://www.duomly.com">![Programming courses online](https://dev-to-uploads.s3.amazonaws.com/i/g743nt7g2577lokq3d8c.jpg)</a>

Thanks for reading,
Radek from Duomly - Programming courses online