SQL: Using Not Exists with Nested Query

Question

I'm having trouble understanding how this query works. It is supposed to return the name of customers who have ordered ALL items.

R refers to the table of item orders made by customers which contains the customer id (cid) and item id (iid).
I refers to the table of items that can be ordered which contains the item id.
C is the customer table with customer id.

SELECT cname
FROM Customer C
WHERE NOT EXISTS
(  (SELECT I.iid
       FROM Item I)
    EXCEPT
   (SELECT R.iid
      FROM Order R
      WHERE R.cid=C.cid))

The bottom nested query with SELECT R.iid gets all the items ordered by any customer.

Then the nested query above the EXCEPT with SELECT I.iid finds all the items which have not been ordered before by subtracting the result of the query below it.

If it is nested, what statement does NOT EXISTS evaluate? Is it R.cid = C.cid because of FROM Customer C? How does it get to the end result?

*It is suppose to return the name of customers who have ordered ALL items.* Yes — Conrad Frix, Feb 27 '13 at 23:07
You should have asked the question without saying what it's supposed to be doing :) — Kaf, Feb 27 '13 at 23:18

score 2 · Answer 1 · answered Feb 27 '13 at 23:14

Break it down. Starting backwards / with the innermost query:

This returns a list of the Item ID's ordered by the customer. (I'm assuming iid stands for Item ID, and is an fk in R)

SELECT R.iid FROM Order R WHERE R.cid=C.cid

This returns a list of item ID's (generally), except for those already ordered by the customer

SELECT I.iid FROM Item I EXCEPT...

Finally, This returns a list of customers who have NOT, NOT ordered one of the items in the list.

SELECT cname FROM Customer C Where NOT EXISTS...

So basically, you're looking for customers who have ordered at least one of everything.

score 0 · Accepted Answer · answered Feb 27 '13 at 23:16

SELECT I.iid FROM Item I means "IDs of all items".

SELECT R.iid FROM Order R WHERE R.cid=C.cid means "IDs of all items that customer C has ever ordered" (where C is determined by the containing query).

In general, query1 EXCEPT query2 means "all rows that are returned by query1 and are not returned by query2". In your specific case, (SELECT I.iid FROM Item I) EXCEPT (SELECT R.iid FROM Order R WHERE R.cid=C.cid) means "IDs of all items that customer C has never ordered".

Inside a NOT EXISTS (subquery) expression, all that really matters is whether subquery returns any rows (in which case it "exists") or not. So (SELECT I.iid FROM Item I) EXCEPT (SELECT R.iid FROM Order R WHERE R.cid=C.cid) exists if there are is any item that customer C has never ordered — and it doesn't exist if there isn't any such item.

So the query as a whole finds the name of customers who have ordered every single item.

SQL: Using Not Exists with Nested Query

2 Answers2