Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?)

share|improve this question
    
The output will be the same, and even the query plan will be the same. (Unless overridden, the query optimizer will always determine the actual join order, regardless of what order you specify the tables in.) –  Nick Chammas Apr 16 '12 at 20:28
    
@nick Chammas Thanks! –  JuanVelez Apr 16 '12 at 20:36
add comment

1 Answer

up vote 8 down vote accepted

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

share|improve this answer
1  
Thank you very much makes sense. That's the answer I was hopping to get. Just been having a rough day with some of my queries today. –  JuanVelez Apr 16 '12 at 20:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.