Understand SQL Join with this simple tutorial with examples

Duomly programming online courses

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 UsersVALUES(1, ‘Radek’, ‘radek@radek.com’, 12),(2, ‘Mark’, ‘mark@mark.com’, 9),(3, ‘Bruce’, ‘bruce@test.com’, 13);

Result:

SQL JOIN | USERS TABLE

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 ShoesVALUES(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

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 userJOIN Shoes shoe ON user.footSize=shoe.shoeSize

where user.name = ‚Radek’

Result:

SQL JOIN EXAMPLE

SQL Join syntax

Basically, SQL Join syntax looks like:

SELECT columnsFROM firstTable JOIN secondTableON columnFromFirstTable = columnFromSecondTableWHERE 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 JOIN | INNER JOIN

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 JOIN | LEFT JOIN

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 JOIN | RIGHT JOIN

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 JOIN | FULL JOIN

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 userINNER JOIN Shoes shoe ON user.footSize=shoe.shoeSizewhere user.name = ‘Radek’

Result:

SQL JOIN | INNER JOIN EXAMPLE

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 userLEFT JOIN Shoes shoe ON user.footSize=shoe.shoeSize

Result:

SQL JOIN | LEFT JOIN EXAMPLE

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 usersRIGHT JOIN shoes ON users.footSize=shoes.shoeSize

Result:

SQL JOIN | RIGHT JOIN EXAMPLE

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 usersFULL JOIN shoes ON users.footSize=shoes.shoeSize

Result:

SQL JOIN | FULL JOIN EXAMPLE

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.

Programming online courses

Thanks for reading,

Radek from Duomly — Programming courses online

--

--

--

www.duomly.com - We believe everyone can learn how to code, so we are making learning fun and easy!

Recommended from Medium

A Brief History Of Low-Code Development

Puzzle game

CRODO -Ambassador program

Correct By Construction

Tangram Flex Correct by Construction

Micro Frontends — foundations — Experts Zone #1 | frontendhouse.com

Building a bot on Slack

Workaround fix Robo3T 1.4 Mac Dark mode UI broken

How to Set Up Ambassador API Gateway with ALB Ingress Controller

How to Fix Discord Overlay Flickering?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Radoslaw Fabisiak

Radoslaw Fabisiak

Core developer in www.duomly.com - Programming online courses

More from Medium

How to Ignore Properties when Unmarshalling a JSON using the Jackson ObjectMapper

How to save JSON data in EXCEL file using Node.js Dynamically

Transferring data from MySQL to MsSQL DataBase

How to create an awesome Github Profile README ?