Left Join Vs Right Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Left Join Vs Right Join

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-03 : 06:11:59

If left join can be converted in right join and right join can be converted in left join then why sql server introduced two types of join.

Can anybody give me explaination.
as i have faced that question in interview

Vabhav T

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-03 : 06:51:37

Well the best reason I can think of to include a RIGHT OUTER JOIN is because it is part of the ANSI standard.

I'd be unhappy with SQL SERVER if it failed to include this standard even though I hardly ever use RIGHT OUTER JOIN (or FULL OUTER JOIN for that matter).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 07:09:09

I am using always left join...
But another question is why not giving both possibilities to the "user"?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 09:38:14

I also tend to use left join always and I cant really think of a situation where I used right join apart from time when i just used to learn it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-03-03 : 10:30:00

I find that in some cases RIGHT JOINs are more readable than nested joins. (Although others disagree!)
eg The following queries are the same.


SELECT *
FROM Table1 T1
	LEFT JOIN
	(
		Table2 T2
			JOIN Table3 T3
				ON T2.T2Id = T3.T2Id
	)
		ON T1.T1Id = T2.T1Id

SELECT *
FROM Table2 T2
	JOIN Table3 T3
		ON T2.T2Id = T3.T2Id
	RIGHT JOIN Table1 T1
		ON T2.T1Id = T1.T1Id

dportas
Yak Posting Veteran

53 Posts

Posted - 2010-03-03 : 15:19:22

In ISO Standard SQL they mean the same thing. It's a silly feature of the standard in my opinion (one among many :-).

In Microsoft SQL Server however there is an odd restriction. The order of ON clauses is not always interchangeable because an ON clause isn't allowed to refer to a table alias defined AFTER the ON clause even though the alias is in the same scope. This can make it difficult to "turn around" some sequences of joins. So it may be that LEFT and RIGHT aren't totally interchangeable in T-SQL, even though they are in Standard SQL. However I can't think of an example right now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources