Optimize SQL query for canceled orders

Question

Here is a subset of my tables:

orders:
 - order_id
 - customer_id

order_products:
 - order_id
 - order_product_id (unique key)
 - canceled

I want to select all orders (order_id) for a given customer(customer_id), where ALL of the products in the order are canceled, not just some of the products. Is there a more elegantly or efficient way of doing it than this:

select order_id from orders
where order_id in (
    select order_id from orders
    inner join order_products on orders.order_id = order_products.order_id
    where order_products.customer_id = 1234 and order_products.canceled = 1
)
and order_id not in (
    select order_id from orders
    inner join order_products on orders.order_id = order_products.order_id
    where order_products.customer_id = 1234 and order_products.canceled = 0
)

Answer 1

If all orders have at least one row in order_products, Try this

 Select order_id from orders o
 Where Not Exists 
      (Select * From order_products 
       Where order_id = o.order_id
          And cancelled = 1)

If the above assumption is not true, then you also need:

 Select order_id from orders o
 Where Exists 
      (Select * From order_products
       Where order_id = o.order_id)
   And Not Exists 
      (Select * From order_products
       Where order_id = o.order_id
          And cancelled = 1)

Answer 2

SELECT customer_id, order_id, count(*) AS product_count, sum(canceled) AS canceled_count
FROM orders JOIN order_products
ON orders.order_id = order_products.order_id
WHERE customer_id = <<VALUE>>
GROUP BY customer_id, order_id
HAVING product_count = canceled_count

Answer 3

The fastest way will be this:

SELECT  order_id
FROM    orders o
WHERE   customer_id = 1234
        AND
        (
        SELECT  canceled
        FROM    order_products op
        WHERE   op.order_id = o.order_id
        ORDER BY
                canceled DESC
        LIMIT 1
        ) = 0

The subquery will return 0 if and only if there had been some products and they all had been canceled.

If there were no products at all, the subquery will return NULL; if there is at least one uncanceled product, the subquery will return 1.

Make sure you have an index on order_products (order_id, canceled)

Answer 4

Something like this? This assumes that every order has at least one product, otherwise this query will return also orders without any products.

 select order_id 
 from orders o
 where not exists (select 1 from order_products op
                   where canceled = 0
                   and op.order_id = o.order_id
 )
 and o.customer_id = 1234

Answer 5

You can try something like this

select  orders.order_id 
from    @orders orders inner join 
     @order_products order_products on orders.order_id = order_products.order_id
where   order_products.customer_id = 1234 
GROUP BY orders.order_id
HAVING SUM(order_products.canceled) = COUNT(order_products.canceled)

Answer 6

Since we don't know the database platform, here's an ANSI standard approach. Note that this assumes nothing about the schema (i.e. data type of the cancelled field, how the cancelled flag is set (i.e. 'YES',1,etc.)) and uses nothing specific to a given database platform (which would likely be a more efficient approach if you could give us the platform and version you are using):

select op1.order_id
from (
   select op.order_id, cast( case when op.cancelled is not null then 1 else 0 end as tinyint) as is_cancelled
   from #order_products op
  ) op1
group by op1.order_id
having count(*) = sum(op1.is_cancelled);