Sql left join multiple tables |What are SQL left join examples ?

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;

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.