So in trying to improve my sql I'm looking to update all my correlated sub queries to self joins. Are there any steps in doing so or does it just depend on each query? I have an example below that is similar to most of the sub queries I'm using, but I'm having a bit of a hard time visualizing how a self join would provide the same result.
SELECT c FROM Clothes c WHERE c.tag IN :tagList
AND (c.timeOfSale = (SELECT MAX(n.timeOfSale) FROM Clothes k
WHERE (c.tag = k.tag) AND (k.timeOfSale) < (:time))) ORDER BY c.tag