Using Inner join after left join in SQL query

Question

I am a bit confusing about applying different joins over multiple tables in a single query. So, I would like to know about the performance & significance of join orders. I have a few doubts about the below query.

1. Shall we write a query like this? i.e. After applying Left Join shall we apply inner join?
2. Once we applied Left Join Should we apply only left joins for the remaining tables in the query?

Select * from Table1 T1   
   LEFT JOIN Table2 T2  ON T1.Id ON T2.Id

   INNER JOIN Table3 T3 ON T3.Id ON T2.Id

Answer 1

You should keep in mind, that the query optimizer can change the statement and the order of the tables if it will be more efficient. In this case you can exchange in the INNER JOIN part the T2.Id with T1.Id because you have defined already that T1.Id equals T2.Id.

So you will have an INNER JOIN between T1 and T3.

create table #t1(Id int, column1 varchar(10));
create table #t2(Id int, column2 varchar(10));
create table #t3(Id int, column3 varchar(10));

Insert into #t1(Id, column1) values (1, 'A'),(2,'B'),   (3, 'C'); 
Insert into #t2(Id, column2) values (1, 'A'),			(3, 'C2'); 
Insert into #t3(Id, column3) values			 			(3, 'C3'); 
--Insert into #t3(Id, column3) values			 (2,'B3'),	(3, 'C3'); 
go
-- Set showplan_text on;
go
Select *
from #t1 as t1
Left Join #t2 as t2 on t1.id = t2.id
Inner Join #t3 as t3 on t3.id = t2.Id;
go
-- Set showplan_text off;
go
drop table #t1;
drop table #t2;
drop table #t3;

You will get one row and the execution plan is:

  |--Hash Match(Inner Join, HASH:([t3].[Id])=([t2].[Id]), RESIDUAL:(#t2.[Id] as [t2].[Id]=#t3.[Id] as [t3].[Id]))
   |--Hash Match(Inner Join, HASH:([t3].[Id])=([t1].[Id]), RESIDUAL:(#t1.[Id] as [t1].[Id]=#t3.[Id] as [t3].[Id]))
   |    |--Table Scan(OBJECT:([tempdb].[dbo].[#t3] AS [t3]))
   |    |--Table Scan(OBJECT:([tempdb].[dbo].[#t1] AS [t1]))
   |--Table Scan(OBJECT:([tempdb].[dbo].[#t2] AS [t2]))

If you change the INNER JOIN to a LEFT JOIN you will get three rows, which is a totally different result.

So using LEFT JOINS or INNER JOINS does not depend on the performance, but on the result you want to get.

If you toggle the INSERT for #t3 you will get a slightly different plan, but in the end it is still an INNER JOIN from T1 and T3. T2 and T3 are joined beforehand.

Answer 2

Hi @Mathankumar Rajendran

You should decide which connection to use based on the output you need.

To answer your questions, you can write a query like that. You can apply different types of joins over multiple tables in a single query, as long as you specify the join conditions correctly.

The order of the joins may affect the performance and execution plan. The SQL optimizer will try to find the best join order based on the statistics and indexes of the tables involved.

Best regards,

Percy Tang