Was thinking about a scenario, suppose two entities, Customer and Order. Suppose I want to see all distinct customers who fulfills a certain criteria, who has one or more orders fulfilling a criteria.
If I use something like:
Select distinct cust from Customer cust join cust.orders order where order.x = 'y' and cust.z = 1
The above statment is slow, as it will perform equality on all fields in the set, causing full table scan, as I understand DBs.
If I instead try:
Select cust from Customer cust where cust.id in(Select distinct(cust.id) from Customer cust join cust.orders order where order.x = 'y' and cust.z = 1)
The above statemtne, seems ok, but I started thinking on where the selection criterias happen, will it happen in DB, or will JPA/Hibernate create all entity instances as the inner statement is executed?
For some reason I have troubles dumping out the SQL created, perhaps theres someone that knows a neater way, or know the cost of the above statement.
Cheers Tomas