Retrieve order numbers without certain items

Question

I have a table with some ice cream order data like so:

id  order_number  item_name
1   1             cake cone
2   1             sprinkles
3   2             sugar cone
4   3             semi-sweet morsels
5   3             sprinkles

(This is simpler than the real table but the relevant columns are present.)

Essentially what I want to do is generate a list of order numbers that don't contain particular items (sugar cones and cake cones).

What I've tried at this point seems to work but I wonder if there is a better way:

select a.order_number from 
(select order_number from orders where (item_name != 'sugar cone' and item_name != 'cake cone') group by order_number)a
left join
(select order_number from orders where (item_name = 'sugar cone' or item_name = 'cake cone') group by order_number)b
on a.order_number = b.order_number
where b.order_number is null;

This will return me the order_numbers I expect (in this case, only order 3) but it seems clunky to me.

Do you have a better way to do this?

Answer 1

select order_number
from orders 
where order_number not in (
    select order_number
    from orders 
    where item_name in ('sprinkles', 'sugar cone')
)

Answer 2

Using an EXISTS clause will give better performance than using an IN clause.

SELECT a.order_number
  FROM orders a
 WHERE NOT EXISTS (SELECT 1
                     FROM orders b
                    WHERE b.item_name IN ('sugar cone', 'cake cone')
                          AND b.order_number = a.order_number);

Answer 3

Using LEFT JOIN/IS NULL:

   SELECT x.order_number
     FROM ORDERS x
LEFT JOIN ORDERS y ON y.order_number = x.order_number
                  AND y.item_name IN ('sprinkles', 'sugar cone')
    WHERE y.id IS NULL

Using NOT EXISTS

   SELECT x.order_number
     FROM ORDERS x
    WHERE NOT EXISTS(SELECT NULL
                       FROM ORDERS y
                      WHERE y.item_name IN ('sprinkles', 'sugar cone')
                        AND y.order_number = x.order_number)

Using NOT IN

   SELECT x.order_number
     FROM ORDERS x
    WHERE x.order_number NOT IN(SELECT y.order_number
                        FROM ORDERS y
                      WHERE y.item_name IN ('sprinkles', 'sugar cone'))

Summary

On MySQL, if the column(s) being joined on are not nullable - LEFT JOIN/IS NULL is the fastest/most efficient. Otherwise, NOT EXISTS & NOT IN are better choices.

Conclusion

Because of joining on a column that can not be NULL, LEFT JOIN/IS NULL is the best choice.

Answer 4

select distinct order_number from orders where order_number not in 
    (select order_number from orders where item_name in ('sugar cone', 'cake cone'))