Change the SELECT in WHERE clause to another solution

Question

I want to make my query better. What opportunities that I have to change this code for a faster one?

SELECT sn
FROM package p
WHERE StatusID = 1
AND ( NOT EXISTS( SELECT * FROM packagedetail pd
                  WHERE pd.packageID = p.ID AND packageDetailStatus = 12 ) 
      OR ( EXISTS ( SELECT * FROM packagedetail pd
                  WHERE pd.packageID = p.ID AND packageDetailStatus = 12) 
            AND  EXISTS ( SELECT * FROM unit u JOIN unitDetail ON u.ID = ud.unitID
                   WHERE (ud.InmostPackageID = p.ID OR ud.OutmostPackageID = p.ID) 
                   AND u.UnitStateID in (8120, 8130, 8140)
                 )
         )
     )

( I know this is so ugly, but I want to know, how can I improve my skills )

Answer 1

One simplification that you can do is to not repeat the NOT EXISTS subquery again.

The below query is semantically equivalent to yours:

SELECT p.sn
FROM package p
WHERE p.StatusID = 1
AND (
  NOT EXISTS(SELECT * FROM packagedetail pd 
             WHERE pd.packageID = p.ID AND packageDetailStatus = 12) 
  OR 
  EXISTS (SELECT * FROM unit u JOIN unitDetail ON u.ID = ud.unitID
          WHERE (ud.InmostPackageID = p.ID OR ud.OutmostPackageID = p.ID) AND u.UnitStateID in (8120, 8130, 8140))
);

because:

(NOT X) OR (X AND Y) = (NOT X) OR Y