Right Join and Left Join acting different

[A Williams]

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.sitei d,w.orgid, mostrecentworkl og.logtype,most recentworklog.d escription,
mostrecentworkl og.siteid,mostr ecentworklog.or gid,mostrecentw orklog.recordke y,
mostrecentworkl og.ldtext,mostr ecentworklog.cr eateby,mostrece ntworklog.creat edate from workorder w
right join (select logtype,descrip tion,siteid,org id,recordkey,ld .ldtext,createb y,createdate from worklog
left join longdescription ld on ld.ldownertable ='worklog' and worklog.worklog id=ld.ldkey
where class='WORKORDE R'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkL og
on MostRecentWorkL og.recordkey=w. wonum and MostRecentWorkL og.siteid=w.sit eid and MostRecentWorkL og.orgid=w.orgi d
where w.siteid='ALE' and w.reportdate>'2 012-01-01'
order by w.wonum

--returns 4138193 records without a where clause, 4267 with the where clause
select w.wonum,w.sitei d,w.orgid, mostrecentworkl og.logtype,most recentworklog.d escription,
mostrecentworkl og.siteid,mostr ecentworklog.or gid,mostrecentw orklog.recordke y,
mostrecentworkl og.ldtext,mostr ecentworklog.cr eateby,mostrece ntworklog.creat edate from workorder w
left outer join (select logtype,descrip tion,siteid,org id,recordkey,ld .ldtext,createb y,createdate from worklog
left join longdescription ld on ld.ldownertable ='worklog' and worklog.worklog id=ld.ldkey
where class='WORKORDE R'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkL og
on w.wonum=MostRec entWorkLog.reco rdkey and w.siteid=MostRe centWorkLog.sit eid and w.orgid=MostRec entWorkLog.orgi d
where w.siteid='ALE' and w.reportdate>'2 012-01-01'
order by w.wonum

[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.

FROM x LEFT JOIN y
FROM y RIGHT JOIN x

These are equivalent.

FROM x LEFT JOIN y
FROM x RIGHT JOIN y

These are not equivalent.

[ck9663]

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