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
Top comments (0)