I am a little confused how the right outer join on #d knows which table is to the right?

Mr_Shaw
Mr_Shaw used Ask the Experts™
on

I am a little confused how the right outer join on #d knows which table is to the right?

select count (*) from
#a left outer join #b on #a.id = #b.id left outer join #c on #c.id = #a.id
right outer join #d on #a.id = #d.id
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the (right) join is based on the condition, so:
>right outer join #d on #a.id = #d.id

means you will have records for #d, even if there are not matching records on #a.
note: I would NEVER mix LEFT and RIGHT joins in the same query, and I actually NEVER use right joins at all.
only LEFT joins ...

also, a COUNT(*) with LEFT/RIGHT joins usually makes no sense, because if you join left/right to count, you don't need the join at all ....

so, I would presumable rewrite the query to:

select ...
from #d
left outer join #a  on #a.id = #d.id
left outer join #b on #a.id = #b.id
left outer join #c on #c.id = #a.id

left is the first mentioned, right the later mention table

so joining #a and #d, #a is left an #d is right





~

Author

Commented:
So in the following query 'left outer join #e on #e.id = #c.id'  c# is the table mentioned earlier so it will be on the left.

select count (*) from
#a left outer join #b on #a.id = #b.id
left outer join #c on #c.id = #a.id
right outer join #d on #a.id = #d.id
left outer join #e on #e.id = #c.id

If left and right joins are based on whatever table is mentioned first. How to I tackle reading a really long sql statement and mentally work out which table is on the left and right?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Hi angelIII,

I was using count(*) just as example select.

I tried the switich the statment around
from: right outer join #d on #a.id = #d.id
to: right outer join #d on #d.id = #a.id

It made no difference.
as Angel says it is better not to mix right and left joins in one statement. I personally have never used a right join
Commented:
yes it is ..seems like you got to used for oracle and using + sign for joins..check the following link..it helps

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
first, let's clarify: are you aware of what really a LEFT and RIGHT (reps INNER) joins are doing?
en.wikipedia.org/wiki/Join_(SQL)

please make sure you understand, and then clarify what is the problem with your query.

Author

Commented:
Yes I understand what they mean.

When I create a query I usually do all the joins using the sql GUI editor. I want to start writing my joins by hand.

When I comes to long querys I was getting a little confused over which table would be conidered Left and Right.

I like the idea of only using LEFT joins.

Author

Commented:
Whenever I read an tutorial of how to do a join the examples only two tables. In the real world more than two tables are needed in most queries.

Commented:
t1 let join t2 let join t3 ..now consider once you analyzed t1 and t2 ,then consider t1 an t2 as ont table ex: T now just think of T and t3 ..so on..
Commented:
t1 left join t2 left join t3 ..now consider once you analyzed t1 and t2 ,then consider t1 an t2 as one table ,let's say T now just think of T and t3 ..so on..

Author

Commented:
thanks
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial