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 )
CodePudding user response:
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