• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

A subquery example in section 6.5.11  RSS feed

 
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Creator of Enthuware JWS+ V6
Posts: 3180
276
Android Chrome Eclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:





I assume if the order's customer = null, it won't be able to know the balance.
 
Himai Minh
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found an example from JPA Pro , chapter 9 jpqlExample.



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
         
employees.JPG
[Thumbnail for employees.JPG]
Employee table
emp_projects.JPG
[Thumbnail for emp_projects.JPG]
employee_projects table
output.JPG
[Thumbnail for output.JPG]
output
 
Himai Minh
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
 
output2.JPG
[Thumbnail for output2.JPG]
output 2
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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.*


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
Ranch Hand
Posts: 1703
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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

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

The output is student id=1,2,3,4.
Student 4 does not have any presentations.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!