When and where should i use left join or right join ?

Question

Post reply

When and where should i use left join or right join ?

Jay Pete

SSCommitted

Points: 1945
More actions
August 14, 2012 at 2:24 pm

#392950

Hi
I am newbie in the T-SQL Programming.
Please anyone can explain to me when and where i should use left join or right join with nice example.
Thanks in advances,
Jay

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you read this?

Using Outer Joins

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

I was going to hit you with a lmgtfy but it actually would have taken longer. You have the keywords, google is your friend.

http://www.datamartist.com/sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GSquared SSC Guru Points: 260824 More actions · Answer 3

Any time you need all of one side of a datum, even if the other side is missing.

All customers and prospects, even those who haven't placed orders, and orders data about the ones who have. You can'd to an inner join from your People (customers + prospects) data set, to your Orders data set, because then you'll only get the ones who have placed orders. But a left join would get you all the People, and get you Orders data for those who have it, and NULL for those who don't.

A right outer join is just the other direction. It's usually a silly distinction, because the order of tables in the From clause shouldn't matter that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Jay Pete (8/14/2012)
Hi
I am newbie in the T-SQL Programming.
Please anyone can explain to me when and where i should use left join or right join with nice example.
Thanks in advances,
Jay

If you vote for Conservatives/Republicans - use RIGHT join

If you vote for Labor/Democrats - use LEFT join

...

You will be surprised that sometimes, depending what and how you joining, you may get no difference at all...

SELECT t1.*, t2.*

FROM t1

LEFT JOIN t2 ON t2.col1 = t1.Col2

is the same as:

SELECT t1.*, t2.*

FROM t2

RIGH JOIN t1 ON t2.col1 = t1.Col2

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Eugene Elutin (8/14/2012)
If you vote for Conservatives/Republicans - use RIGHT join
If you vote for Labor/Democrats - use LEFT join

I exclusively use LEFT JOINs. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St