Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am required to retrieve the list of pno ( product_id ) for the products which were purchased by two customers. For this, I have been given two relations which are ( along with the attributes ) as follows.


Here ono is the order_id, pno is the product_id, qty is the quantity of the product in stock, cno is the customer_id of the customer who is purchasing the product, eno is the employee_id who is selling the product, received is the date on which the customer received the product and shipped is the date when the product was shipped. ono is the primary key of orders and foreign key of odetails. ono, pno, qty form the primary key of odetails.

One query is:-

select distinct d.pno 
from odetails d,orders o1,orders o1 
where d.ono=o1.ono 
  and d.ono=o2.ono 
  and o1.cno<o2.cno;

Another query is:

select distinct d1.pno 
from odetails d1,odetails d2,orders o1,orders o1 
where d1.ono=d2.ono 
  and d1.ono=o1.ono 
  and d2.ono=o2.ono and o1.cno<o2.cno;

Could you please tell me what is the difference between these two queries? Both of them seem to work in the same way except the fact the second query seems to be doing extra and useless work of comparing two odetails when they are not even required to do so.

share|improve this question
What exactly are your requirements? What do you want the queries to return? Products that have been purchased by 2 (or more) customers and not those by one customer? – ypercubeᵀᴹ Aug 21 '13 at 15:21
Have you actually tried these queries? You either have something different in the question than the actual queries you have run or you have not tried them at all. Both queries will always return 0 rows. – ypercubeᵀᴹ Aug 21 '13 at 20:20
Only the second one seems close to what you describe - and only if you replace where d1.ono=d2.ono with where d1.pno=d2.pno – ypercubeᵀᴹ Aug 21 '13 at 20:22
up vote 3 down vote accepted

Consider the following odetails data:

ono   pno
1     1
1     2

If you join it on itself by ono, like

FROM odetails d1 JOIN odetails d2 ON d1.ono = d2.ono;

then you will get four rows instead of two. So the two queries are different in their meaning even though they return the same data (thanks to the DISTINCT keyword).

(There is a possibility for similar problem with joining the orders table twice. As you did not mention the exact requirements for this query, one can't be sure if even the first query is correct. (Acknowledgment to ypercube for mentioning this.))

share|improve this answer
So, does this mean that the first query is more efficient or something else? I think that creating four rows when you can work just fine with two is needless,right? – kusur Aug 21 '13 at 12:30
Right. But my point is that doing more work isn't the real problem; it is doing it wrong. (In this case it makes no difference but there may be others when it would do.) – dezso Aug 21 '13 at 12:33
@dezso: see my comments in the question. – ypercubeᵀᴹ Aug 21 '13 at 20:24

You ask Oracle to self-join odetails (d1.ono=d2.ono) which is not in the 1st query. So, it is more job to do this.

share|improve this answer
Yes, this is what 'except the fact the second query seems to be doing extra and useless work of comparing two odetails' says to me. – dezso Aug 21 '13 at 12:13
That is what I am thinking. But is there any difference in how they both will differ in their functioning ? Is the extra work being done in the second query weeding out some unexpected behaviour ? – kusur Aug 21 '13 at 12:27
Yes, it can have performance impact: If you have multiple same ono in odetails, then oracle will join these rows then reduce it with the distinct. This is extra work to do, and if you have a lot of data, it will used the TEMP tablespace. Rember that more IOs is more Waits – Nicolas Durand Aug 21 '13 at 12:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.