Q:

Optimize SQL query for canceled orders

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
)

A:

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)

