Sql left join multiple tables

In my previous articles I have given idea about different types of Joins with examples. In this article I would like to give you idea about the SQL left join multiple tables with its examples. The main use of SQL left join multiple tables is to connect to multiple tables to achieve specific set of data. These kind of sql joins are useful in reporting as well as database development to achieve specific functionality. In this article I would like to give you simple to complex examples of Sql left join multiple tables which are used in industry.

What is Left Join?

Before going to detailed examples of left join I would like to give you idea about left join.

When user wants to retrieve all the records from left table and common records from both table together then left join is used.

Syntax :

Type 1: With using (+) Symbol

Select t1.column1,t2.column2….t ‘n’column ‘n.’.

from table1 t1,table2 t2

where t1.column=t2.column(+);

Type 2: With using Left Outer join keyword

Select t1.column1,t2.column2….t ‘n’column ‘n.’.

from table1 t1 Left Outer join table2 t2

on t1.column=t2.column;

Sql left join multiple tables

Simple Example of Left Join :

In this section we will check very simple example of left join before checking example of sql left join multiple tables in detail. Kindly check following 2 tables.

Employee Table :

Employee Id Employee Name Department Id
1 Amit 233
2 Saideepti 233
3 Purna 244
4 Rahul 245

Department Table :

Department ID Department Name
233 Business Intelligence
234 Development
244 Application support
235 Database support

Problem Statement :

Need to fetch data for Employees with Associated departments. If Department is not associated then that should display as null.

Query :

Select A.Employee_name,B.Department_name from

Employee A,Departemnt B

Where A.Department_id =B.Department_Id (+);

Output :

Employee Name Department Name
Amit Business Intelligence
Saideepti Business Intelligence
Purna Application support
Rahul  

For Employee named Rahul the condition is not matching. So the department is showing blank.

Sql left join multiple tables

In this section we will look one complex SQL left join multiple tables example. The first question in users mind is why we require sql left join multiple tables and purpose of it. There are following situations where we require SQL left join multiple tables.

1.Creating Reports

The Sql joins with multiple tables are more required to fetch the useful business data. This data is useful for creating different BI reports.

2.PL SQL Development

In PL SQL development where user requires to fetch data from multiple tables.

3.Solve Business requirement from database side

To resolve any business requirement from database side SQL joins with multiple tables are used.

4.Dashboard creation

SQL left join multiple tables are used to create interactive dashboards using SQL Queries.

5.Performance Tuning

SQL Left join is faster than the Inline view. So SQL left joins are used to improve performance of application.

Example :

You can refer same 2 tables with following additional table for fetching data in 3 tables.

Employee Table :

Employee Id Employee Name Department Id
1 Amit 233
2 Saideepti 233
3 Purna 244
4 Rahul 245

Department Table :

Department ID Department Name
233 Business Intelligence
234 Development
244 Application support
235 Database support

Salary Table :

Employee Id Salary
1 89000
2 60000
3 45000
4 50000

Problem Statement :

We need to fetch data from Employee table with its salary and associated department. If Department is not associated with Employee need to show that Blank.

Select A.Employee_Name,B.Salary,C.Department_Name

From Employee A

Left Join

Salary B on A.Employee_Id=B.Employee_Id

Left Join

Department C On A.Department_Id = C.Department_ID;

Query Explanation Step-by-Step :

Step 1 : Employee and Salary table join to fetch Employee and its associated Salary.

Step 2 : Use that set and join that set with Department table to fetch department associated with employee.

Output :

Employee Name Salary Department Name
Amit 89000 Business Intelligence
Saideepti 60000 Business Intelligence
Purna 45000 Application Support
Rahul 50000  

I hope this article is useful to you.If you like this article or if you have any queries with this article kindly comment in comments section.

2 Replies to “Sql left join multiple tables”

Leave a Reply

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