Home Ask Login Register

Developers Planet

Your answer is one click away!

Sam January 2017

Steps for converting a correlated subquery to self join

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


Gurwinder January 2017

You can find max of timeofSale for each tag and join it with the main table like this:

select c
from clothes c
inner join (
  SELECT tag, MAX(timeOfSale) timeOfScale
  FROM Clothes
  WHERE timeOfSale < :time
  group by tag
) k on c.tag = k.tag
and c.timeOfScale = k.timeOfScale;

Post Status

Asked in January 2017
Viewed 3,251 times
Voted 6
Answered 1 times


Leave an answer

Quote of the day: live life