sql - 优化已取消订单的 SQL 查询

Question

这是我的表格的一个子集:

orders:
 - order_id
 - customer_id

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

我想选择给定客户 (customer_id) 的所有订单 (order_id)，其中订单中的所有产品都已取消，而不仅仅是部分产品。有没有比这更优雅或更有效的方法:

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

如果所有订单在 order_products 中至少有一行，试试这个

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

如果上述假设不成立，那么你还需要:

 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)