469,282 Members | 1,824 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Right Join and Left Join acting different

I have two queries, one using a right join and one using a left join that are producing different results, but they should be the same. The RIGHT join is acting like an inner join.

Please see the queries below . . . In the first one I have the workorder (w) table on the RIGHT side of the ON statement and a RIGHT join. In the second one, I have the the workorder (w) table on the LEFT side of the ON statement and a LEFT join, so these two queries should return the same results, but they aren't.

What am I missing??


--returns 53198 records without a where clause, 394 with the where clause
select w.wonum,w.siteid,w.orgid, mostrecentworklog.logtype,mostrecentworklog.descri ption,
mostrecentworklog.siteid,mostrecentworklog.orgid,m ostrecentworklog.recordkey,
mostrecentworklog.ldtext,mostrecentworklog.createb y,mostrecentworklog.createdate from workorder w
right join (select logtype,description,siteid,orgid,recordkey,ld.ldte xt,createby,createdate from worklog
left join longdescription ld on ld.ldownertable='worklog' and worklog.worklogid=ld.ldkey
where class='WORKORDER'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkLog
on MostRecentWorkLog.recordkey=w.wonum and MostRecentWorkLog.siteid=w.siteid and MostRecentWorkLog.orgid=w.orgid
where w.siteid='ALE' and w.reportdate>'2012-01-01'
order by w.wonum

--returns 4138193 records without a where clause, 4267 with the where clause
select w.wonum,w.siteid,w.orgid, mostrecentworklog.logtype,mostrecentworklog.descri ption,
mostrecentworklog.siteid,mostrecentworklog.orgid,m ostrecentworklog.recordkey,
mostrecentworklog.ldtext,mostrecentworklog.createb y,mostrecentworklog.createdate from workorder w
left outer join (select logtype,description,siteid,orgid,recordkey,ld.ldte xt,createby,createdate from worklog
left join longdescription ld on ld.ldownertable='worklog' and worklog.worklogid=ld.ldkey
where class='WORKORDER'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkLog
on w.wonum=MostRecentWorkLog.recordkey and w.siteid=MostRecentWorkLog.siteid and w.orgid=MostRecentWorkLog.orgid
where w.siteid='ALE' and w.reportdate>'2012-01-01'
order by w.wonum
Feb 8 '12 #1

✓ answered by Rabbit

Your joined sources are in the same place. You seem to think that it is the ON part of the join statement that dictates which one is left and which one is right. But that is not the case.
Expand|Select|Wrap|Line Numbers
  1. FROM x LEFT JOIN y
  2. FROM y RIGHT JOIN x
These are equivalent.
Expand|Select|Wrap|Line Numbers
  1. FROM x LEFT JOIN y
  2. FROM x RIGHT JOIN y
These are not equivalent.

2 1588
Rabbit
12,516 Expert Mod 8TB
Your joined sources are in the same place. You seem to think that it is the ON part of the join statement that dictates which one is left and which one is right. But that is not the case.
Expand|Select|Wrap|Line Numbers
  1. FROM x LEFT JOIN y
  2. FROM y RIGHT JOIN x
These are equivalent.
Expand|Select|Wrap|Line Numbers
  1. FROM x LEFT JOIN y
  2. FROM x RIGHT JOIN y
These are not equivalent.
Feb 8 '12 #2
ck9663
2,878 Expert 2GB
Remember this: A LEFT join with a where clause using a column on the right table or a RIGHT join with a where clause using a column on the left table is actually an INNER JOIN...

Happy Coding!!!

~~ CK
Feb 8 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Charles Haven | last post: by
2 posts views Thread by michael | last post: by
reply views Thread by Jon Trelfa | last post: by
1 post views Thread by Paul Bramscher | last post: by
3 posts views Thread by rrstudio2 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.