Sql performance query multiple conditions on same column

Question

I have a table ORDERS with ORDER_ID and SERVICE_ID field.

I can have multiple entries in table like:

ID   ORDER_ID   SERVICE_ID
1     000001         1
2     000001         2
3     000001         3

4     000002         1
5     000002         2

I need to final all orders that have entries for service_id 2 but not for service_id 3. My query is:

SELECT * FROM ORDERS
WHERE SERVICE_ID = 2
  AND ORDER_ID NOT IN (SELECT ORDER_ID FROM ORDERS WHERE SERVICE_ID = 3) 

Is there another way to do this in order to improve performance?

Thanx

question from:https://stackoverflow.com/questions/65843827/sql-performance-query-multiple-conditions-on-same-column

Answer 1

Use NOT EXISTS which generally performs better than NOT IN:

SELECT * FROM ORDERS O
WHERE O.SERVICE_ID = 2
  AND NOT EXISTS 
      (SELECT 1 FROM ORDERS OO WHERE OO.ORDER_ID = O.ORDER_ID AND OO.SERVICE_ID = 3)