How do I do a JPQL SubQuery?

Question

It is possible to do the equivalent of this sql query in JPQL?

SELECT * 
FROM   COUNTRIES c WHERE COUNTRY_ID IN (
SELECT DISTINCT COUNTRY_ID 
FROM PORTS p 
WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
) 

Answer 1

You need to test it with IN and subquery since both do work in JPQL (according to syntax reference they do work together). You may also look at MEMBER OF expressions.

But there is a better approach in my opinion. Such queries are called correlated sub-queries and one can always re-write them using EXISTS:

SELECT * 
FROM   COUNTRIES c WHERE EXISTS 
           ( SELECT 'found' FROM PORTS p 
             WHERE p.COUNTRY_ID = c.COUNTRY_ID AND STATE = 'A'
           ) 

JPQL supports EXISTS with subqueries.