difference of two queries

khdani · #1 (**permalink**) 11-14-09, 09:46

hello,
i don't understand why those two queries don't give the same
result:
1)

Code:

select name 
from customer 
where custid in
(select custid
from ord
where ordid in (
	(select ordid
	from item,product
	where item.prodid = product.prodid
	and product.descrip like '%TENNIS%')
	minus
	(select ordid
	from item,product
	where item.prodid = product.prodid
	and product.descrip not like '%TENNIS%')
));

2)

Code:

select name
from customer
where custid in
	(select custid 
	from ord
	where ordid in (
		(select ordid
		from item,product
		where item.prodid = product.prodid
		and product.descrip like '%TENNIS%')))
minus
select name
from customer
where custid in
	(select custid 
	from ord
	where ordid in (
		(select ordid
		from item,product
		where item.prodid = product.prodid
		and product.descrip not like '%TENNIS%')));

they should give the same result but they don't....

r937 · #2 (**permalink**) 11-14-09, 10:07

that's right, they don't

nor should they

perhaps you can explain why you think they should

khdani · #3 (**permalink**) 11-14-09, 10:26

in (1) this subquery

Code:

(select ordid
	from item,product
	where item.prodid = product.prodid
	and product.descrip like '&#37;TENNIS%')
	minus
	(select ordid
	from item,product
	where item.prodid = product.prodid
	and product.descrip not like '%TENNIS%')

should return ordid of those ordids who ordered items with desctiption which contains the word 'TENNIS' minus those with description which does not contains the word 'TENNIS'
and get the name of customers with those ordids.

in (2) i first get the names who ordered items with desctiption which contains the word
'TENNIS' minus names who ordered items with description which does not contains the word 'TENNIS'.

isn't the same ?

r937 · #4 (**permalink**) 11-14-09, 10:38

query 1 returns customers who have any orders for ~only~ tennis items

query 2 returns customers who never ordered any non-tennis items

look at this sample data --

customer AAA order #123 contains only a tennis racquet
customer AAA order #456 contains a tennis racquet and a football

customer BBB order #789 contains a tennis racquet and tennis shorts
customer BBB order #555 contains tennis balls

query 1 returns both AAA and BBB

query 2 returns only BBB

khdani · #5 (**permalink**) 11-14-09, 10:43

i think i got it,
thank you for explaining

khdani · #6 (**permalink**) 11-14-09, 10:44

i think i got it,
thank you for explaining

Internet Services

Web Development

Digital Marketing

Consumer Tech