Exists versus IN
-
Can anyone tell me why I get two different result sets with the following queries?
The first one returns records that should not be returned.
Note the difference is exists vs in
select VENDOR,UPC_EAN,DESCRIPTION from item_master im
join vendor_item vi on vi.item_id=im.item_id
join vendor_master vm on vm.v_id=vi.v_id
where (warehouse_item is null or rtrim(ltrim(warehouse_item)) = '') and vendor = '031151' and im.store_pos_department = '005' AND vi.RECORD_STATUS<>3
AND exists
(SELECT UPC_EAN FROM ITEM_MASTER IM2
join vendor_item vi2 on vi2.item_id=im2.item_id
join vendor_master vm2 on vm2.v_id=vi2.v_id
WHERE VENDOR in ('601340','608471'))
ORDER BY upc_EAN
versus
select VENDOR,UPC_EAN,DESCRIPTION from item_master im
join vendor_item vi on vi.item_id=im.item_id
join vendor_master vm on vm.v_id=vi.v_id
where (warehouse_item is null or rtrim(ltrim(warehouse_item)) = '') and vendor = '031151' and im.store_pos_department = '005' AND vi.RECORD_STATUS<>3
AND upc_ean in
(SELECT UPC_EAN FROM ITEM_MASTER IM2
join vendor_item vi2 on vi2.item_id=im2.item_id
join vendor_master vm2 on vm2.v_id=vi2.v_id
WHERE VENDOR in ('601340','608471'))
ORDER BY upc_EAN
Edited by - csherwood on 06/30/2003 2:28:33 PM
-
Long, long time ago, someone posted comparable problems in a thread about using IN vs EXISTS.
If I recall correctly, you should fully qualify the UPC_EAN in the EXISTS clause. Otherwise SQL Server will take the UPC_EAN code from the main table, which always exists, obviously.
So try
...
AND exists
(SELECT IM2.UPC_EAN FROM ITEM_MASTER IM2
... -
That did not work. Also, if you remove the or ltrim(rtrim(warehouse_item)) = '' part of the main query it will work (of course not returning the warehouse_items that have a space as a value)
-
Strange...
Maybe same problem with the WHERE clause:
Add the IM2.Vendor to the subquery in your EXISTS clause.
-
That did not work either.
It is interesting to note that upcs are being returned that do not exist when running the subquery separately. It will work correctly if you add "and im2.item_id = im.item_id", but I am curious as to why it won't work as is. Logically it seems that it should. I'm afraid that I may not understand all that I think I do about exists.
-
Yep, your right, stupid me
...You should of course add a 'join' on the UPC_EAN code to the WHERE of the EXISTS.
... AND exists
(SELECT IM2.UPC_EAN FROM ITEM_MASTER IM2
join vendor_item vi2 on vi2.item_id=im2.item_id
join vendor_master vm2 on vm2.v_id=vi2.v_id
WHERE IM2.VENDOR in ('601340','608471')
AND IM2.UPC_EAN = IM.UPC_EAN)
ORDER BY upc_EAN(Maybe Vendor is in another table, but I can't see that from your posts.)
But my previous posts still hold
-
If you run the following query Upc 0000000000500 is not returned. So why when you are saying and exists (which it does not) does it get returned in the original query??
The upc_ean is in the Item Master table
The vendor is in the Vendor_master table
and the Vendor Item table is what joins the two together via item_id and v_id
SELECT IM2.UPC_EAN FROM ITEM_MASTER IM2join vendor_item vi2 on vi2.item_id=im2.item_idjoin vendor_master vm2 on vm2.v_id=vi2.v_idWHERE IM2.VENDOR in ('601340','608471')
-
OK. Don't know if this is an answer to my post or not? Did you try that and did it return the right data? If not, try posting some (relevant) data that shows your problem. Just the main columns is sufficient...
Anyway, you can compare EXISTS to IN in the sense that you have to compare a field in the outer query to one in the subquery. That's why you'll have to add the second condition to the WHERE clause.
-
You will note in my previous posts I said that the join of the sub query to the main query DID work.
At any rate, it is working, but I'm still curious about why it did not without the qualifying clause. Here is a snapshot of the results
This is not the whole result set, but since it is in upc order it should be enough
Query without the qualifying and im2.item_id = im.item_id
VENDOR UPC_EAN description
---------- ------------- ------------------------------------
031151 0000000000500 RUM RUNNER SINGLE
031151 0000000000501 RUM RUNNER BOX
031151 0000000000503 MI ROBUSTO
... and so on
query with the qualifying clause
VENDOR UPC_EAN description
---------- ------------- ------------------------------------
031151 0000866000002 ZIGZAG ORANGE ROLLING PAPER
031151 0001230000093 CAMEL MENTHOL BOX KING-PA
031151 0001230000094 CAMEL LT KING BOX MENTHOL
... and so on
Subquery by itself
UPC_EAN
-------------
0000000123223
0000000733131
0000006000002
0000087000002
0000087000003
0000087000004
0000087010003
0000123027299
0000272000608
0000288000502
0000351065707
0000430002048
0000866000002
... and so on
Note upc 0000000000500 is not in the Subquery results by itself, so
since the query contains and exists in (), which it does not why does it
show up?
-
The main difference is this.
IN restricts the list of values to only those within the IN clause in matching. So in this case the subquery will return a list of values and when you say "upc_ean in" you effectively say it the upc_ean has to be found in that list returned from the subquery.
EXISTS means that at least one value is returned from the subquery. SO in your case if
SELECT UPC_EAN FROM ITEM_MASTER IM2
join vendor_item vi2 on vi2.item_id=im2.item_id
join vendor_master vm2 on vm2.v_id=vi2.v_id
WHERE VENDOR in ('601340','608471')
will output at least one record thus the condition of EXISTS is true and all values will be return. You are not doing anything to limit the list.
So IN means is in the records returned to be true, EXISTS means the query returns at least one record to be true.
-
Antares686
So basically you are saying that if any record is returned by the subquery regardless of what it is then it will evaluate to true. So by adding the qualifying clause "and im2.item_id = im.item_id" so is restricting the result set of the subquery.
That is helpful to know, I guess I never understood that before.
Thank you all for the help.
-
That's it.
...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply