SQL Joins you should know
Tags: sql, database, beginners, postgres
Published: 2021-08-14 14:41:20

![sql joins you should know](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rsu6ijc700bkdou9jdhd.png)

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
![Inner Join Sql tablee](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1b9v5flzitbd4denrzev.png)
and lets say we also have another table of *instagram* likes for some users
![Inner Join Sql table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/948db0bm4rsgx3dqhwys.png)

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
![Inner Join ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/atuxzqqqu0v0v1svpohs.png)

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

![Left Joined Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vrvgt2gel6a1zcwb08sv.png)

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
![Right Join table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d6xg60ypcvflm6o5c3kt.png)

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

![Full Outer Join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zabwsjvg0styf0x95rxy.png)

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](https://www.linkedin.com/in/junaid-shah-ba2910a9/)