SQL Joins you should know

We all as developers know what a database is , right
Its a way of storing your data whether it be text , images , audio or video databases can be used to save all kinds of data and over the years databases have evolved from having just SQL to now having databases like Mongo db .

But the basics of database can be learnt every time in order to better understand how databases work.

So we know that a table is something that stores the table in a particular structure called columns

So e.g we can have a table for login where in we can store the email and password of the user to authenticate him every time he tries to login and so on

Joins come into play when we have to join(as the name suggests) two tables in order to get a table that has some common ground(that we tell in the join itself ).
So the most used joins that there are right now are

Inner Join (also called as simply JOIN)
Left Join
Right Join
Outer Join( also called FULL OUTER JOIN)

So lets go over each one of them one by one

Inner Join

Inner join also can be called as simply “join” is a way getting data out of two tables that would be common among both the tables.
This can be better understood by an example
Lets just say we have a facebook table Like this

and lets say we also have another table of instagram likes for some users

Now the way we would use inner join on these two is like this

SELECT * FROM facebook JOIN instagram ON facebook.name = instagram.name

So the way this works is we are telling SQL to join both the tables and create a new table that has all the entries that are common to both these tables and satisfy the criteria that is facebook.name = instagram.name , everything else will not be included in that table.

So katie and kim will not be included in the join table.
The Inner Join table will look like this then

Left Join

Left join is another join that we can use to join the tables in a way that all the values of the left table are going to come in the joined table irrespective of if they have a match or not .

If they don't have a match a null value is passed in that column

Let's just take the previous example again.
If we use a left join on those two tables , the query will look like this

SELECT * FROM facebook LEFT JOIN instagram ON facebook.name = instagram.name

Take a moment and think how the resulted table will look like .
OK , so the table will look like this.

Ok, so you can see that the first table in the query thats Facebook , its all columns are put in the table where as in the Instagram table , since there was not any match for that column of Facebook name thats “kim” , so the column are put as null.

If you would have written the query as Instagram first and then Facebook , then the query would have done the opposite of what it did here .

It would have taken the Instagram table put all its columns and when there would have been no match for any column the field would have been put as null.

The opposite that i talked about here is what can also be called as RIGHT JOIN.

Right join is exactly what a left join is , but opposite .

What it will do is take the right table instead of left and do the same thing that it did in the left join .
Right join can also be written as a left join as well (if you can visualise it , you get it).
Lets do i Right join on the same exact query we did for the left join

SELECT * FROM facebook RIGHT JOIN instagram ON facebook.name = instagram.name

Now the table will look like this

This is the exact same as like this

SELECT * FROM instagram LEFT JOIN facebook ON facebook.name = instagram.name

So as i said left join can also be written as right join and vice versa.

FULL OUTER JOIN

FULL OUTER JOIN is a way of joining tables such that both the tables columns will be available and whenever there is no match in the next column a null is placed for that .
Lets take the previous example and do a full outer join on that one

SELECT * FROM facebook FULL OUTER JOIN instagram ON facebook.name = instagram.name

So the joined table will look like this

So as you can see in the joined table whenever there is no match a null is placed in the adjacent column for that .
OK , so thats about it for JOINS in tables .
I hope you guys got some value out of it and learnt something new today.
You can always reach to me at Junaid shah