15 Subquery

A subquery may be non-correlated to its parent query, or correlate to a root or join of its parent query.

Non-Correlated

If a subquery is not correlated to its parent query, its results will be independent from its parent query, and it needs to be evaluated only once.

Example: select all full-time employees whose salaries are above average.

	CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
	CriteriaQuery<FullTimeEmployee> criteriaQuery = criteriaBuilder.createQuery(FullTimeEmployee.class);
	Root<FullTimeEmployee> root = criteriaQuery.from(FullTimeEmployee.class);
	criteriaQuery.select(root);
	
	Subquery<Double> subquery = criteriaQuery.subquery(Double.class);
	Root<FullTimeEmployee> subqueryRoot = subquery.from(FullTimeEmployee.class);
	subquery.select(criteriaBuilder.avg(subqueryRoot.get(FullTimeEmployee_.salary)));
	
	criteriaQuery.where(criteriaBuilder.greaterThan(
			criteriaBuilder.toDouble(root.get(FullTimeEmployee_.salary)), subquery));
	
	TypedQuery<FullTimeEmployee> query = em.createQuery(criteriaQuery);
	List<FullTimeEmployee> resultList = query.getResultList();

Equivalent JPQL:

	TypedQuery<Employee> query = em.createQuery("select e from FullTimeEmployee e " +
				"where e.salary > (select avg(e.salary) from FullTimeEmployee e)", Employee.class);
	List<Employer> resultList = query.getResultList();

Correlate To Root

Example: select employers that have more than N full time employees.

	EntityManager em = ...; // see "Get Started" on how to create an EntityManager
	CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
	CriteriaQuery<Employer> criteriaQuery = criteriaBuilder.createQuery(Employer.class);
	Root<Employer> root = criteriaQuery.from(Employer.class);
	criteriaQuery.select(root);
	criteriaQuery.distinct(true);

Subquery: correlate the root of parent query

	Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
	Root<Employer> subqueryRoot = subquery.correlate(root);
	ListJoin<Employer, Employee> employees = subqueryRoot.joinList("employees");
	subquery.where(criteriaBuilder.equal(employees.type(), FullTimeEmployee.class));
	subquery.select(criteriaBuilder.countDistinct(employees));

Add where clause: subquery is an expression

	// more than 100 employees
	Predicate restriction = criteriaBuilder.greaterThan(subquery, 100L);
	criteriaQuery.where(restriction);

Execute the query

	TypedQuery<Employer> query = em.createQuery(criteriaQuery);
	List<Employer> resultList = query.getResultList();

Equivalent JPQL:

	TypedQuery<Employer> query = em.createQuery("select e from Employer e " +
			"where (select count(distinct emp) from e.employees emp where type(emp)=FullTimeEmployee) > 100", Employer.class);
	List<Employer> resultList = query.getResultList();

Correlate To Join

Example: select employers with any employee who has more than N phone numbers.

	CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
	CriteriaQuery<Employer> criteriaQuery = criteriaBuilder.createQuery(Employer.class);
	Root<Employer> root = criteriaQuery.from(Employer.class);
	ListJoin<Employer, Employee> employees = root.joinList(Employer_.employees);
	criteriaQuery.select(root);
	criteriaQuery.distinct(true);

Subquery: correlate a Join of parent query

	Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
	// correlate join
	ListJoin<Employer, Employee> correlatedEmployees = subquery.correlate(employees);
	ListJoin<Employee, PhoneNumber> phoneNumbers = correlatedEmployees.join(Employee_.phoneNumbers);
	subquery.select(criteriaBuilder.countDistinct(phoneNumbers));
	
	// count(phoneNumber) > 5
	Predicate restriction = criteriaBuilder.greaterThan(subquery, 5L);
	criteriaQuery.where(restriction);
	
	TypedQuery<Employer> query = em.createQuery(criteriaQuery);
	List<Employer> resultList = query.getResultList();

Equivalent JPQL:

	TypedQuery<Employer> query = em.createQuery("select distinct e from Employer e join e.employees emp " +
		"where (select count(distinct pn) from emp.phoneNumbers pn) > 5", Employer.class);
	List<Employer> resultList = query.getResultList();

Some, Any, All, Exists

Some/any/all/exists are used with subquery in where or having clause. Some(subquery) is the same as any(subquery).

Example: select all employees who join a Large Project.

	TypedQuery<Employee> query = em.createQuery("select e from Employee e " +
		"where e.id = any (select e.id from LargeProject lp inner join lp.employees e)", Employee.class);
	List<Employee> resultList = query.getResultList();

Example: select all employees who join a Large Project.

	TypedQuery<Employee> query = em.createQuery("select e from Employee e " +
		"where exists (select p from e.projects p where type(p)=SmallProject) ", Employee.class);
	List<Employee> resultList = query.getResultList();

Example: select all full-time employees who are hired after all part-time employees.

	TypedQuery<Employee> query = em.createQuery("select e from FullTimeEmployee e " +
		"where e.hiredDate > all (select e.hiredDate from PartTimeEmployee e)", Employee.class);
	List<Employee> resultList = query.getResultList();

Not all databases support some/any/all/exists (subquery).

← Stored Procedure→ Entity ListenersFrames / No Frames