Today we’ll talk about SQL Join.
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', '[email protected]', 12), (2, 'Mark', '[email protected]', 9), (3, 'Bruce', '[email protected]', 13);
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';
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.
The first one is the INNER JOIN method that will pick rows that fit conditions from the first and the second table.
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.
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.
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'
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
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
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
Here we can see we have all, it doesn’t matter if shoes have potential owners or users have the possibility of buy shoes.
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