Difference between the given queries

oracle-10gselect

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.

odetails(ono,pno,qty)
orders(ono,cno,eno,received,shipped)

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.

Best Answer

Consider the following odetails data:

ono   pno
---------
1     1
1     2

If you join it on itself by ono, like

SELECT pno
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.))

Related Question