What is Join in SQL | 7 Types of Join | Inner Join, Full Join, Left Join, Right Join

SQL stands for Structured Query Language where Join is an important concept to merge two data sets by using primary key or unique key. SQL is based on the concept of Relational Algebra. In other words, it can be said the it is the application of Relational Algebra. SQL was developed by Raymond Boyce & Donald Chamberlin in 1970s, those were researcher in IBM. In the early days, SQL was called as SEQUEL which is stands for Structured English Query Language. So, lets deep dive into concept, what is join in SQL. Also, we’ll discuss 7 types of join. Not only in SQL but also in other data management or data analytics tools, join concept is being used. Best way to start learning SQL and database concept is to get familiar with Microsoft access.

After SQL, NoSQL came into the picture to deal with non structured or non-relational data.

What is Join in SQL?

To know what is join in SQL, let’s use one example to understand join. Suppose in Table 1, we have following columns.

Employee Id, Name and Joining date.

We need age and annual salary in that table, but it is in the different table (Table 2).

In both the table, employee id is the unique id or primary key. So, in this case both the table should get merged by using employee id where the objective was to have all the columns in a single table. This is the concept of join where two table get join by using a scientific logic.

As Emp id 25 and 21 are not present in table 1 & 2 and not common in both the tables, it is not featuring in the table after joining. Here, Inner Join has been used which will be described later.

Employee Id, Name, Joining date, Age, Annual salary

Types of Join in SQL

Basically, there are four types of join, inner join, outer or full join, left join & right join. Apart from that there are another three types of join, full outer join without inter section in SQL, left outer join without inter section in SQL, right outer join without inter section in SQL.

i) Inner Join in SQL

In case of inner join, algorithm looks for two tables and join those two tables by using common unique ids from the id column of two tables. In Venn diagram, blue part is showing the common part from the two table T1 & T2.

Refer to the example used in ‘What is Join’ discussion.

SQL Code:

SELECT Table1.Emp_Id, Table2.Age… ## or *

FROM Table1

INNER JOIN Table2

ON Table1.Emp_Id = Table2.Emp_Id;

ii) Full Outer Join in SQL

Outer join is also called full join. In case of full join between two tables, all the rows from two tables get merged into one table by using the unique id column.

Let’s say, we have two tables, Table 1 & Table 2. By using Full outer join, it will create a table with all the rows are present in Table 1 & Table 2.

As id 25 and 21 was not common in both the table, in case of id 21, it is not showing name and joining date. In case of id 25, it is not showing age and salary.

The concept of full outer join is shown in the form of Venn diagram where entire T1 (Table 1) & T2 (Table 2) get merged [blue area].

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table2
FULL OUTER JOIN Table2 ON Table1.Emp_Id=Table2_Emp_Id
ORDER BY Table1.Name;

iii) Left Join in SQL

Keep all record of Table 1 as per the condition.

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table1
LEFT JOIN Table2 ON Table1.Emp_Id = Table2.Emp_Id
ORDER BY Table1.Name;

iv) Right Join in SQL

Keep all record of Table 2 as per the condition.

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table1
RIGHT JOIN Table2 ON Table1.Emp_Id = Table2.Emp_Id
ORDER BY Table1.Name;

v) Full Outer Join without inter section in SQL

Keep all records of Table 1 and Table 2 excluding the common records between Table 1 and 2.

In other words, Outer Join – Inner Join

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table1
FULL JOIN Table2 ON Table1.Emp_Id = Table2.Emp_Id
WHERE Table1.Emp_Id IS NULL OR Table2.Emp_Id IS NULL;

vi) Left Outer Join without inter section in SQL

Advertisement

Keep all records of Table 1 excluding the common records between Table 1 & 2.

In other words, Left Join – Inner Join

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table1
LEFT JOIN Table2 ON Table1.Emp_Id = Table2.Emp_Id
WHERE Table2.Emp_Id IS NULL;

vii) Right Outer Join without inter section in SQL

Keep all records of Table 2 excluding the common records between Table 1 & 2.

In other words, Right Join – Inner Join

SQL Code:

SELECT Table1.Emp_Id, Table2.Age ## or *
FROM Table1
RIGHT JOIN Table2 ON Table1.Emp_Id = Table2.Emp_Id
WHERE Table1.Emp_Id IS NULL;

Now we know that what is join in SQL and basic types of join. Let’s know the other types of joins in SQL.

What is Natural Join in SQL?

Natural Join is a type of join where algorithm looks for common column names present in the two columns and then join those tables. In such case, it could be inner join, outer join, left join or right join. Tough it is referring to same column name, difference of spelling or extra spaces will be taken when algorithm will be considered the column name. In SQL, ‘*’ is being used to perform natural join.

What is Self Join in SQL?

The name self join define itself the methodology or type of join. Self join also called regular join. In this case, tables are joining with itself, in other words, rows are getting combined with other rows of table itself. The usefulness of self join are, find out duplicate data, correlate or find out relation of data.

What is Equi Join in SQL?

Equi join is a kind of join methodology where tables are getting combined depending on the values which are matching and in a specific column. If columns names are not same but contain same values, equi join can be done only different column names will appear in the output table. Not only two table but also equi join can be done with more than two tables.

In this article we have discussed that what is join in SQL with example and visualization. So, to master in the join concept, practice more and get into the real world data.

Please follow and like us: