How To Join 3 Tables in SQL :
In my previous article I have given different SQL joining examples.In this article i would like to give information about How to join 3 tables in SQL with examples.If you dont know the joins its really very difficult how to join 3 tables in SQL.So the main question in users mind will be How to Join 3 tables in SQL and where it is used.There are so many situations where user needs to fetch the data from two or more tables.Those are the cases where user needs to join the 3 tables.The joining 3 tables is one of the most important scenario in the SQL.
What is Join in SQL with example?
Before going to check about How to Join 3 Tables in SQL user needs to know about the concept of join with example. I have already explained the concept of SQL join in my article. In this section i just would like to give single liner definition of different join with one syntax and example.
1.Inner Join :
Inner Joins is nothing but fetching the common records from two or more tables.
Syntax :
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2
where t1.col=t2.col;
Example :
Select a.Department_ID,a.Department_Name from
Employee b,Department a
where a.Department_ID=b.Department_ID;
2.Outer Join :
Outer join in SQL is nothing but fetching the common records from two or more table and all records from either left table or right table.
There are two types of outer join in SQL :
1.Left outer Join
2.Right outer Join
Left outer Join :
Left outer join in SQL is nothing but fetching the common records from two or more tables and all records from Left table.
Syntax :
Type 1:Left Outer Join Syntax with +
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2
where t1.col=t2.col(+);
Type 2:With Keyword
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 Left Outer join table2 t2
on t1.col=t2.col;
Example :
Select a.Employee_name,b.Department_Name from
Employee a,Department b
where a.Department_ID=b.Department_ID(+);
Right outer Join :
Right outer join in SQL is nothing but fetching the common records from two or more tables and all records from Right table.
Syntax :
Type 1:Right Outer Join Syntax with +
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2
where t1.col(+)=t2.col;
Type 2:With Keyword
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 Right Outer join table2 t2
on t1.col=t2.col;
Example :
Select a.Employee_name,b.Department_Name from
Employee a,Department b
where a.Department_ID(+)=b.Department_ID;
3.Cross Join Or Cartesian Join :
The cross join is the join where each and every table value can join with every other value from other table.
Where condition is missing or wrong in Cartesian join.
Syntax :
Type 1:Right Outer Join Syntax with +
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2;
Example :
Select a.Employee_name,b.Department_Name from
Employee a,Department b;
How to Join 3 Tables in SQL Example :
In this section i would like to give you information about How to join 3 tables in SQL with real world industry example.I hope you get the common idea about how to join 2 tables with examples.There are so many ways using which user can fetch the records for multiple tables. The first and most simple way to fetch the data is using Subqueries and correlated subqueries. I would like to give you the solution got How to join 3 tables in SQL which will work in most of important database programming Languages like PostgreSQL,Microsoft SQL Server or MS SQL as well.
Syntax :
SELECT t1.column, t2.column,t3.column FROM
table1 join table2 ON table1.primarykey =table2.foreignkey
join table3 ON table2.primarykey =table3.foreignkey;
The table1 and table2 creates new temporary table. With taht temporary table the table 3 is joining. It is taking the common records from 3 tables which are table1,table2 and table3 e.t.c.
The joining of 3 tables is important scenario in most of reporting tools and techniques.In reporting user needs to fetch the data from multiple tables and using the concept of joining 3 tables user can achieve this easy way.
Real World Industry Example :
In this section i would like to explain the example of How to join the 3 tables in SQL thoroughly.I have explained the syntax of joining 3 tables in SQL in above section. If user wants the records from multiple tables then concept of joining 3 tables is important.
Let us take the example of Joining 3 tables. If user wants to join tables named Employees,Department and Salary to fetch the Employee name and salary then following queries are helpful.
SQL > SELECT * FROM Employees;
+——–+————-+
| emp_id | emp_name |
+——–+————-+
| 1001| Amit Sharma |
| 2002| Rohit Patil |
| 3003| Rajiv Shukl |
| 4004| Ram Rahim |
+——–+————-+
Lets say, Employees table fetched the list of above employees.
SQL> SELECT * FROM Department;
+———+———–+
| dept_id | emp_id |
+———+———–+
| 101 | 1001 |
| 102 | 2002 |
| 103 | 3003 |
|104 | 4004 |
+———+———–+
Lets say, Department table contains dept_id and emp_id of employee.
SQL> SELECT * FROM Salary;
+——–+———+
| emp_id | salary |
+——–+———+
|1001 | 10000 |
|2002 | 15000 |
|3003 | 25000 |
|4004 | 30000 |
+——–+———+
Lets say, Salary table contains emp_id and salary of employee.
If user wants to fetch emp_name,dept_no and salary for the employees then following query will be helpful,
SQL> SELECT e.emp_name, s.salary FROM Employee e JOIN Salary s ON e.emp_id=s.emp_id JOIN Department d ON s.emp_id=d.emp_id;
+———-+———–+——–+—–+
| emp_name | dept_no | Salary |
+———-+———–+——–+
| Amit Sharma | 101 | 10000 |
| Rohit Patil | 102 | 15000 |
| Rajiv Shukl | 103 | 25000 |
| Ram Rahim | 104 | 30000 |
+———-+———–+——-+——+
This is the most important example of How to join 3 tables in SQL. User can join multiple tables with multiple real world scenarios.
I hope you like this article on How to Join 3 tables in SQL. I have explained the real world industry example with the same. If you like this article or if you have any issues or concerns with the same kindly comment it in to comments section.