SQL Server – Optimizing Table Order in Join Query

join;sql server

If I say:

Table1 left join Table2

is it the same as saying?:

Table2 right join Table1

In other words, should I expect to get the same results from 2 identical queries where the only thing that is changed is which table is written first and whether a left or right join is used (following the same pattern I have described above?)

Best Answer

Yes, the results will be the same. Take a look at this as an example:

if exists(select * from sys.tables where name = 'T1')
    drop table T1
go
if exists(select * from sys.tables where name = 'T2')
    drop table T2
go

create table T1
(
    id int not null,
    someText varchar(100) not null
)
go

insert into T1
values(1, 'hello'),
(3, 'bye'),
(6, 'what')
go

create table T2
(
    id int not null,
    someText varchar (100) not null
)
go

insert into T2
values(2, 'hi'),
(3, 'ciao'),
(4, 'no')
go

select *
from T1
left join T2
on T1.id = T2.id

select *
from T2
right join T1
on T2.id = T1.id

The output will have the same exact fields (mind you, if you use SELECT * the column order will be different between the queries) with the same exact data.

As a reference, here are the two execution plans:

enter image description here

Related Question