Java.Hibernate.Medium.Can we use subqueries in JPQL ?

✅ Short Answer

Yes — JPQL supports subqueries, but only in the WHERE and HAVING clauses, not in the FROM clause.

---

🔎 Detailed Explanation

🔹 Where you can use subqueries in JPQL
✅ WHERE clause → use a subquery to filter parent entities based on data from another entity.
✅ HAVING clause → use subqueries with aggregates to filter grouped results.

❌ FROM clause → JPQL does not support subqueries in FROM, unlike SQL.

---

🔹 Example: Subquery in WHERE
Find customers who have orders over $10,000:

String jpql = """
    SELECT c FROM Customer c
    WHERE EXISTS (
        SELECT o FROM Order o
        WHERE o.customer = c AND o.total > 10000
    )
    """;
List<Customer> richCustomers = em.createQuery(jpql, Customer.class).getResultList();

✅ Here, the subquery inside EXISTS filters customers with qualifying orders.

🔹 Example: Subquery with IN
Find products ordered in orders from a specific country:

String jpql = """
    SELECT p FROM Product p
    WHERE p.id IN (
        SELECT oi.product.id FROM OrderItem oi
        WHERE oi.order.customer.country = :country
    )
    """;
List<Product> products = em.createQuery(jpql, Product.class)
                           .setParameter("country", "USA")
                           .getResultList();

📊 Quick Facts about JPQL Subqueries

Supported Location	Supported?	Notes
WHERE clause	✅ Yes	Most common use case
HAVING clause	✅ Yes	For aggregated filters
FROM clause	❌ Not allowed	JPQL doesn’t support derived tables/subselects in FROM

🔹 What if you need FROM subqueries?

• You must switch to native SQL → use createNativeQuery() if your query absolutely requires subqueries in the FROM clause.

---

📌 Key Takeaways

✅ JPQL supports subqueries in WHERE and HAVING → great for filtering based on related or aggregated data.
❌ JPQL does not allow subqueries in the FROM clause.
✅ For FROM subqueries, use native SQL queries.