tags:

views:

45

answers:

4

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?

+2  A: 
select order_number
from orders 
where order_number not in (
    select order_number
    from orders 
    where item_name in ('sprinkles', 'sugar cone')
)
RedFilter
+1 - quick on the draw!
mdma
@RedFilter - I don't think this statement will yield the correct results. You are negating rows based on the id rather than the order_number.
Tom
@Tom: you are correct, typo fixed
RedFilter
What if order_number is NULL?
gbn
@gbn: if order number can be NULL (doubtful in this case) then you can use NOT EXISTS
RedFilter
+3  A: 

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);
Tom
+1 and deals with NULLs, and also no give duplicates like a JOIN could do
gbn
+3  A: 

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.

OMG Ponies
@OMG Ponies I think there is a mistake in the WHERE clause of your NOT EXISTS solution. Do you not mean y.order_number = x.order_number?
Tom
@Tom: Corrected, thx.
OMG Ponies
@OMG Ponies Actually, is your conclusion correct in light of your correction? Technically you aren't joining on the primary key any more. Even so, I guess order_number would be a FK with an index, so the point is possibly moot.
Tom
@Tom: The issue is nullability--the ability for a column to allow a value to be `NULL`--, not being a primary key or indexes.
OMG Ponies
+1  A: 
select distinct order_number from orders where order_number not in 
    (select order_number from orders where item_name in ('sugar cone', 'cake cone'))
ovais.tariq