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.
Table of Contents
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.
Table 1
Emp_Id
Name
Joining Date
43
Steve
4-Apr-18
31
Mike
6-Jul-15
25
Nicolas
6-Feb-14
52
Bob
7-Oct-19
48
Joe
8-Feb-19
We need age and annual salary in that table, but it is in the different table (Table 2).
Table 2
Emp_Id
Age
Annual Salary
43
32
1,000,500
31
31
800,645
21
28
900,520
52
25
600,870
48
30
1,001,500
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.
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.
Table 1
Emp_Id
Name
Joining Date
43
Steve
4-Apr-18
31
Mike
6-Jul-15
25
Nicolas
6-Feb-14
52
Bob
7-Oct-19
48
Joe
8-Feb-19
Table 2
Emp_Id
Age
Annual Salary
43
32
1,000,500
31
31
800,645
21
28
900,520
52
25
600,870
48
30
1,001,500
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.
Table 1 & Table 2
Emp_Id
Name
Joining Date
Age
Annual Salary
43
Steve
4-Apr-18
32
1,000,500
31
Mike
6-Jul-15
31
800,645
25
Nicolas
6-Feb-14
21
28
900,520
52
Bob
7-Oct-19
25
600,870
48
Joe
8-Feb-19
30
1,001,500
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
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.
1 thought on “What is Join in SQL | 7 Types of Join | Inner Join, Full Join, Left Join, Right Join”