4

We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item:

customer     item  
Sally        1   
Sally        2  
Sally        3
John         1  
John         2  
Paul         2
Paul         3

We want to find users who bought "1" and not "3". In this case we only want "John".

1
  • 3
    but Sally has bought item 3. Oct 16, 2014 at 11:05

4 Answers 4

10

No, a self join would not really help, but if your database system has an EXCEPT operator (or something similar, in Oracle it is called MINUS) it would.

Here's how you get your result:

SELECT customer FROM Sales
WHERE item = 1
AND customer NOT IN (SELECT customer FROM Sales WHERE item = 3)

If you have an EXCEPT operator:

SELECT customer FROM Sales WHERE item = 1
EXCEPT
SELECT customer FROM Sales WHERE item = 3
4
  • minus is Oracle specific. The SQL standard defines except Oct 16, 2014 at 11:45
  • Correct. At least I am not alone in assuming it were the other way around. I fixed it.
    – Twinkles
    Oct 16, 2014 at 11:50
  • I think this is because MINUS was introduced in Oracle long before this was defined in the SQL standard. Oct 16, 2014 at 11:52
  • 3
    "No, a self join would not really help" That's not entirely correct. Your first, NOT IN solution is actually an anti-semi(self)join. It could well be written with a self LEFT join (and WHERE .. NOT NULL check). Oct 16, 2014 at 13:00
4

Another approach using aggregation:

SELECT customer 
FROM sales
GROUP BY customer
HAVING MAX( CASE item WHEN 1 THEN 1
                      WHEN 3 THEN 2
                             ELSE 0 END ) = 1;

the Case converts the item-list into 1 for the good item and 2 for the unwanted item, every other item to zero. The maximum will ten be 1 if a good item is in the list for a customer, 2 if a bad item is in the list, 0 otherwise.

3

In this many-to-many relation between customers and items, you want to find customers with item A and with nonexistent item B. To add a "row does not exist" condition to an SQL query, you can use a LEFT JOIN, which represents nonexistent rows as NULL values, followed by a WHERE some_NOT_NULL_column IS NULL. In a nonexistence join, the ON clause has to completely specify the criteria for what row needs to not exist, which may mean a longer ON clause than some people are used to seeing. Thus the solution using a left join, as suggested by ypercube's comment, becomes as follows:

SELECT o1.customer
FROM orders AS o1
LEFT JOIN orders AS o2
ON (o1.customer = o2.customer AND o2.item = 3)
WHERE o1.item = 1
  AND o2.customer IS NULL
1
  • This would be my choice, although probably with DISTINCT for any customer who had more than one purchase of item 1.
    – Kickstart
    Oct 16, 2014 at 15:51
1

You could use a self join, but I would recommend using EXISTS (or in this case, NOT EXISTS) instead (and it is more efficient than using IN):

SELECT
    *
FROM
    foo
WHERE
    item = 1
    AND NOT EXISTS (SELECT 1 FROM foo AS f WHERE f.customer = foo.customer AND item = 3);
1
  • 2
    Whether a [NOT] EXISTS subquery is more efficient than a [NOT] IN subquery depends how the DBMS optimizes such queries.
    – Air
    Oct 16, 2014 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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