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.

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

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.

1 thought on “Joins in SQL – Inner, Outer, Left and Right Join”

  1. Is the result of full outer join example wrong?
    for rows which CountryId == 5 and 6, the result is:
    CountryId CountryName Stateid Statename
    NULL NULL 03 KATHMANDU
    NULL NULL 04 ISLAMABAD

    does the result should be:
    CountryId CountryName Stateid Statename
    5 NULL 03 KATHMANDU
    6 NULL 04 ISLAMABAD

Leave a Comment

Your email address will not be published. Required fields are marked *