473,382 Members | 1,386 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

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 1723
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

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

Similar topics

0
by: Charles Haven | last post by:
Hello all, Is it possible to LEFT JOIN a table to itself? I want to see all records in a table where the year of the sales date is 2003 and where the salesman sold an item to a customer to...
2
by: michael | last post by:
Gotta post because this is driving me nuts. Trying to DELETE orphans. I can successfully: SELECT GroupID FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE UsersID IS NULL; but when I...
0
by: Jon Trelfa | last post by:
I'm having difficulties getting all rows to return from the left table on a left join. For some reason, i only get the rows where there is a match between the right and the left table. Here's...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
5
by: jason.evans | last post by:
Hi there. I am having an intrigueing problem. I have a query which left joins another query to itself twice. The original query is derived from a linked table in SQLServer 2000. When I run...
2
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since...
2
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList)...
1
by: CstmrSrvc | last post by:
I'm not sure if this is the right forum, so please forgive me if I"m off. I'm doing a query from MS Excel using the Query Wizard. I'm pulling from 4 different JDE Tables. I need to join AND...
3
by: rrstudio2 | last post by:
If I have two tables and need to do a left outer join and include a where statement on the second table, it seems like the left outer join becomes an inner join. For example: Table: Names id...
4
by: markcarroll | last post by:
Right now I have the following SQL query inside of an access database: It works, but it runs VERY slowly. I figure I could speed it up if I could inculde the WHERE conditions as part of of the...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.