Can nested query in JPQL access outer query

Question

So I'm curious if a nested SELECT can reference it's outer SELECT in order to compare values. I haven't been able to test or see many examples on this topic.

As an example, I'm trying to write a query to select all Clothes rows that has a tag (some number) that is within a given list and has the highest time that is prior to given time (which is total number of seconds). The query in question is below:

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))) GROUP BY c.tag

Is the comparison c.tag = k.tag valid? If not, is there an alternative?

Answer 1

Yes. They're called correlated queries, where the subquery is evaluated for each row of outer query.

Answer 2

@Query("SELECT b FROM Business b WHERE b <> :currentBusiness "
        + "and exists "
        + "(Select i from InterestMaster i, BusinessInterest bI where bI.interestMaster = i and bI.business = b"
        + "and i in (:userInterests))")
Page<Business> getCommunityBusiness(@Param("currentBusiness") Business currentBusiness, @Param("userInterests") List<InterestMaster> userInterests,Pageable pageable);

I am using the above JPQL and its working fine. So yes nested query can access outer query.