title: SQL - most efficient way to find if a pair of row does NOT exist
body: <p>I can't seem to find a similar situation to mine online. I have a table for 'orders' called Order, and a table for details on those orders, called 'order detail'. The definition of a certain type of order is if it has 1 of two pairs of order details (Value-Unit pairs). So, my order detail table might look like this:</p>
  
  <pre><code>order_id | detail
  ---------|-------
  1        | X  
  1        | Y
  1        | Z
  2        | X
  2        | Z
  2        | B
  3        | A
  3        | Z
  3        | B
  </code></pre>
  
  <p>The two pairs that go together are (X &amp; Y) and (A &amp; B). What is an efficient way of retrieving only those order_ids that DO NOT contain either one of these pairs? e.g. For the above table, I need to receive only the order_id 2.</p>
  
  <p>The only solution I can come up with is essentially to use two queries and perform a self join:</p>
  
  <pre><code>select distinct o.order_id
  from orders o
  where o.order_id not in (
      select distinct order_id
      from order_detail od1 where od1.detail=X
      join order_detail od2 on od2.order_id = od1.order_id and od2.detail=Y
  ) 
  and o.order_id not in (
      select distinct order_id
      from order_detail od1 where od1.detail=A
      join order_detail od2 on od2.order_id = od1.order_id and od2.detail=B
  )
  </code></pre>
  
  <p>The problem is that performance is an issue, my order_detail table is HUGE, and I am quite inexperienced in query languages. Is there a faster way to do this with a lower cardinality? I also have zero control over the schema of the tables, so I can't change anything there.</p>