A subquery example in section 6.5.11

[Himai Minh]

In section 6.5.11 of the spec:

The first query will return orders that are not associated with customers and the second one will not
1. select o from Order o where 10000<ALL (Select a.balance from o.customer c join c.account a)
2. select o from Order o JOIN o.customer c where 10000< ALL(Select a.balance from c.accounts a)

I think  query 1  will return order that are associated with customers.
For query 1 ,   the sub query will return all orders associated with customers like this table:

customer id | order id | account  id | account balance|

Then, we select all the order where account balance > 10000.
Since Order inner join with Customer in the sub query, there is not any order that is not associated with any customer.

[Frits Walraven]

I think  query 1  will return order that are associated with customers.  

No, just read the note slighty above:

Note that joins involving the derived subquery root do not affect the join conditions of the containing query.

For example:

It this first query ( select o from Order o where 10000<ALL (Select a.balance from o.customer c join c.account a) ) you can have 10 orders of which 8 are associated with customers and 2 are not. Let's say that those 8 orders have a balance below 10.000 then the query will return all 10 orders.>

[Himai Minh]

That is strange.
I guess the where clause in query 1 will filter out the account balance that is less than 10000.
So, only the orders which are associated with customers and their accounts balance > 1000 are returned.

Logically, is it  selecting the orders which customers' account balance is > 10000 ?

[Himai Minh]

One thing I want to add:
if there are orders that are not associated with any customers, we won't know the balance of that order. It is because the balance is related to a customer's account.
I assume the relationship in this example is like this:

@Entity
public class Order[
@id
private int id;
@oneToOne   //unidirectional relationship with a customer
private Customer customer;
 ...
}

@Entity
public class Customer{
  ...
@oneToOne
private Account account; ...
}

@Entity
public class Account{

private long balance;
}

I assume if the order's customer = null, it won't be able to know the balance.

[Himai Minh]

I found an example from JPA Pro , chapter 9 jpqlExample.

@Entity
 
public class Employee {
    @Id
    private int id;
    private String name;
    private long salary;
    ...
    
    @ManyToOne
    private Employee manager;
    
    @OneToMany(mappedBy="manager")
    private Collection<Employee> directs = new ArrayList<Employee>();
    
    @ManyToMany(mappedBy="employees")
    private Collection<Project> projects = new ArrayList<Project>();
...

@Entity
 ...
public class Project {
    @Id
    protected int id;
    protected String name;
    @ManyToMany
    protected Collection<Employee> employees = new ArrayList<Employee>();
    ...

In the DB, there are only 3 managers with id = 9,10,11.
Query 1 : SELECT e FROM Employee e WHERE 55000 < ALL (SELECT m.salary FROM e.manager m JOIN m.projects p)
(Return those employees whose manager with a project and salary is > 55000)

Result:
Those employees, whose manager is manager id =10 Joan with salary is 59000, are in the result;
Those employees, whose manager is manager id =9 Sarah with  salary is 52000, are not in the result;
Employee 12 Joe whose manager is 11 and he has no project, is still in the result.
Those without a manager are still in the result.

I guess the join of the subquery , manager inner join project ,generates this:
                      m.project           e.manager
                         3                    9                //not in the  output as manager 9 has salary <55000
                          9                    9                //not output
                         7                    10                 // in the output, as manager 10 has salary>55000
                         8                    10                  //in the output          
                         9                    10                   //in the output

    To my surprise , employees with no manager or no project are in the output. There is  WHERE 55000 > ... clause that should filter out those employees.
     I guess all the employees are returned from the containing query first. Then, the subquery is executed with the join.
    And this subquery's join has no effect on the containing query.
      The output of the containing query still include:        
        employee    projects             manager id
        12              no project           11    
         8                 8,2                     null
         10               7,8,9                  null                        
         11               no project          null
          13               no project         null
         

[Himai Minh]

Query 2 SELECT e FROM Employee e JOIN e.projects p WHERE 55000 < ALL (SELECT m.salary FROM e.manager m)
Employee inner join projects , which generates the query , same as the employee_projects table.
Then, select those employee whose managers' salary > 55000.
Result:
 Those employees with manager id=10 whose salary is 59000 are in the output.

 Those with manager id =9 whose salary is 52000 are not in the output.
  Those with manager id =11 whose salary is 3600 are not in the output.
  Those employee who don't have any project are not in the output due to the inner join. Those employees who are not in employee_projects table are not returned.

  But, those without a manager are still in the output. I guess the employee inner join projects containing query is executed first. The subquery encounters those manager =null cases, but has no effect on the containing query.
 
 

[Jeff Lindner]

   SELECT o    FROM Order o    WHERE 10000 < ALL (        SELECT a.balance        FROM o.customer c JOIN c.accounts a)

1. The outer query is run first.  The outer query selects all orders, including orders that are not associated with a customer (e.g. orders fulfilled where buyer chose not to register as a customer).

2. Next, the subquery is run for each record selected in the outer query.

   - If the order has a customer, the subquery returns the balances of all accounts associated with the customer.  If all balances are < 10000 the ALL condition evaluates to TRUE and the row of the outer query is included in the result set.

   - If a subquery returns zero rows (which would happen in this example if if there is no customer associated with the order) the ALL condition evaluates to TRUE and the row is included in the result.

*The orders in the first query are not associated with customers and therefore the result set will include both orders that have customers and orders that were processed for people who decided not to register as customers.*

   SELECT o        FROM Order o JOIN o.customer c        WHERE 10000 < ALL (            SELECT a.balance            FROM c.accounts a)
1.  The outer query is run first.  The outer query selects only orders that are associated with customers.

2.  Next, the subquery is run for each row.

   - If the customer associated with the order has one or more accounts and all of the account balances are< 10000 the ALL condition will evaluate to true and the row in the outer query is included in the result set.

   - If the customer associated with the order has no accounts the subquery returns zero rows the ALL condition evaluates to true and the row in the outer query is included in the result set.

*The orders in the second query are associated with customers and therefore the result set will not include orders that were processed for people who decided not to register as customers.*

[Himai Minh]

Hi, everyone,
I have just found a nice graphical tool from Enthuware that allows us to build JPQL and criteria query. The only issue is that we cannot put generics.
Here is another example to demo correlate subquery.
Example 1, returning only those students who have presentations (which has a one-to-many relationship:

//select  s.enrollment.dept  from Student s where exists (select ps from s.presentations ps)
 CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery c = cb.createQuery(String.class);
Root student = c.from (Student.class);
 
    Subquery sq = c.subquery(Presentation.class);
    Root s = sq.correlate(student);
    Join ps = s.join("presentations");
    sq.select(ps);

c.select(student.get("id")).where (cb.exists(sq));
TypedQuery tq = em.createQuery(c);
List result = tq.getResultList();

This returns student id = 1,2,3 who has presentations.

Example 2, returning all students whenever there is a presentation.

//select s.enrollment.dept from Student s where exists (select p from Student s join s.presentations p)
 
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery c = cb.createQuery(String.class);
Root student = c.from (Student.class);
   
    //This sub query is not related to the main query.
    Subquery sq = c.subquery(Presentation.class);
    Root s = sq.from(Student.class);
    Join ps = s.join("presentations");
    sq.select(ps);

c.select(student.get("enrollment").get("dept")).where (cb.exists(sq));
TypedQuery tq = em.createQuery(c);
List result = tq.getResultList();

The output is student id=1,2,3,4.
Student 4 does not have any presentations.