Joins in SQL – Inner, Outer, Left and Right Join

Here you will get detailed information about joins in SQL.

Join is a very basic concept in SQL which can be confusing at times. The joins are used when we need to find out the solution to a query involving the attributes of more than one table which have at least one attribute in common. Hence the need of join is pretty much clear in itself. There are different types of join which are used for different purposes.

Joins in SQL

Let’s say we have two tables, a table named STUDENT and the other table named TEACHER.

The first table “STUDENT” stores the reference details of the student and the second table “TEACHER” stores the details of the teachers who are in the school and the class they are teaching.

STUDENT

ClassID	Name	House address
101	Raj	xyz
201	Varun	byc
301	Chittiz	abc
401	Hema	def

TEACHER

Teacher id	Class name	ClassID
1	First	101
2	Second	201
3	Third	301
4	Fourth	401

In the second table, the “ClassID” is a foreign key which is used as a reference of the first table in the second one.

Now, if we want to find out the name of the student whose teacher id is 1; we need to find the join of the above mentioned tables since it requires us to gather the information of both the tables. Hence, the joins are only used where both the tables have at least one attribute (here ClassID) in common and we need to find the solution to a query which involves the attributes from both the tables.

Types of Join

Basically there are four types of joins namely, inner join, left join, right join, full outer join. The explanation of each one of the mentioned joins is as given below.

1. Inner Join

Let us consider the following two tables, the first table’s name is Country (saves the id of different countries) and the other table’s name is State (saves the various states in those countries).

COUNTRY

CountryId	CountryName
1	China
2	India
3	USA

STATE

StateId	CountryId	StateName
01	2	GOA
02	2	RAJASTHAN
03	5	KATHMANDU
04	6	ISLAMABAD

select * from COUNTRY

inner join STATE

on COUNTRY.CountryId=STATE.CountryId

The above mentioned command applies an inner join on the two table, since the common attribute is Country id, we have applied the join on the same.

The inner join returns all the matching values from both the tables. Here, in table State, since the only matching CountryId value in the country table is {CountryId = 2}, as a result of the inner join we will get the following result:

CountryId	CountryName	StateId	StateName
2	India	01	GOA
2	India	02	RAJASTHAN

2. Right Join

The  right (or right outer join) on the other hand, displays the data which is common in both the tables, as well as the data which is present in the right table (exclusively).

This basically means that the entire right table’s data would be displayed on application of a right join.

When there is no match in the Left Table, it displays NULL.

Example:

COUNTRY

CountryId	CountryName
1	China
2	India
3	USA

STATE

StateId	CountryId	StateName
01	2	GOA
02	2	RAJASTHAN
03	5	KATHMANDU
04	6	ISLAMABAD

select * from COUNTRY

right  join STATE

on COUNTRY.CountryId=STATE.CountryId

The above mentioned command applies a right join on the two tables, since the common attribute is CountryId; we have applied the join on CountryId itself.

The right table is the second table we refer to.

We would get the following table as a result of the application of the right join:

CountryId	CountryName	StateId	StateName
2	India	01	GOA
2	India	02	RAJASTHAN
5	NULL	03	KATHMANDU
6	NULL	04	ISLAMABAD

In the result, it is clearly depicted that the values in the Left Table which have absolutely no matching values in the Right one are not being displayed. Only those values of the Left Table are displayed which have common attributes with the Right one. Whereas all the values in the Right Table are displayed. The rows in the Right Table with no match in the Left Table are displayed as NULL (Empty).

3. Left Join

The left join (or left outer join) on the other hand, displays the data which is common in both the tables, as well as the data which is present in the left table (exclusively).

This basically means that the entire Left Table’s data would be displayed on application of a Left Join.

When there is no match in the Left Table, it displays NULL.

COUNTRY

CountryId	CountryName
1	China
2	India
3	USA

STATE

StateId	CountryId	StateName
01	2	GOA
02	2	RAJASTHAN
03	5	KATHMANDU
04	6	ISLAMABAD

select * from COUNTRY

left join STATE

on COUNTRY.CountryId =STATE.CountryId

The above mentioned command applies a Left Join on the two tables, since the common attribute is CountryId; we have applied the join on Countryid itself.

The Left Table is the first table we refer to.

We would get the following table as a result on application of left join:

CountryId	CountryName	Stateid	Statename
1	China	NULL	NULL
2	India	01	GOA
2	India	02	RAJASTHAN
3	USA	NULL	NULL

In the result, it is clearly depicted that the values in the right column which have absolutely no matching values in the left one are not being displayed. Only those attributes of the right column are displayed which have common attributes with the left one. Whereas all the values in the Left Table are displayed. The rows in the Left Table with no match in the Right Table are displayed as NULL (Empty)

4. Full Outer Join

The Full Outer Join, as the name suggests, shows all the content of both the tables. The Full Outer Join returns all matching records from both the tables whether the other table matches or not.

COUNTRY

CountryId	CountryName
1	China
2	India
3	USA

STATE

StateId	CountryId	StateName
01	2	GOA
02	2	RAJASTHAN
03	5	KATHMANDU
04	6	CANADA

select * from COUNTRY

full outer join STATE

on COUNTRY.CountryId=TEACHER.CountryId

The above mentioned command applies a Full Outer Join on the two tables, since the common attribute is CountryId; we have applied the join on Countryid itself.

We would get the following table as a result on application of Full Outer Join:

CountryId	CountryName	Stateid	Statename
1	China	NULL	NULL
2	India	01	GOA
2	India	02	RAJASTHAN
3	USA	NULL	NULL
NULL	NULL	03	KATHMANDU
NULL	NULL	04	ISLAMABAD

This Join results in all the rows. We get NULL (Empty), when there is no match.

Joins are essentially important to work with tables in SQL and the above mentioned description genuinely elaborate the usage of each one of them.

Comment down below if you have any queries related to SQL joins.