Skip Headers

# Subselects and Subqueries

Previous

Next

Some queries compare the results of other, contained queries (or subqueries). SQL supports this comparison through subselects. TopLink expressions provide subqueries to support subselects.

Subqueries lets you define complex expressions that query on aggregated values (`counts`, `min`, `max`) and unrelated objects (`exists`, `in`, `comparisons`). To obtain a subquery, pass an instance of a report query to any expression comparison operation, or use the `subQuery` operation on an expression builder. The subquery is not required to have the same reference class as the parent query, and it must use its own expression builder.

You can nest subqueries, or use them in parallel. Subqueries can also make use of custom SQL.

For expression comparison operations that accept a single value (`equal`, `greaterThan`, `lessThan`), the subquery result must return a single value. For expression comparison operations that accept a set of values (`in`, `exists`), the subquery result must return a set of values.

Example: A Subquery Expression Using a Comparison and Count Operation illustrates how to create an expression that matches all employees with more than five managed employees.

A Subquery Expression Using a Comparison and Count Operation

    ExpressionBuilder emp = new ExpressionBuilder();
    ExpressionBuilder managedEmp = new ExpressionBuilder();
    ReportQuery subQuery = new ReportQuery(Employee.class, managedEmp);
    subQuery.addCount();
    subQuery.setSelectionCriteria(managedEmp.get("manager") .equal(emp));
    Expression exp = emp.subQuery(subQuery).greaterThan(5);

Example: A Subquery Expression Using a Comparison and Max Operation illustrates how to create an expression that matches the employee with the highest salary in the city of Ottawa.

A Subquery Expression Using a Comparison and Max Operation

    ExpressionBuilder emp = new ExpressionBuilder();
    ExpressionBuilder ottawaEmp = new ExpressionBuilder();
    ReportQuery subQuery = new ReportQuery(Employee.class, ottawaEmp);
    subQuery.addMax("salary");
    subQuery.setSelectionCriteria(ottawaEmp.get("address").get("city").equal("Ottawa"));
    Expression exp =   emp.get("salary").equal(subQuery).and(emp.get("address").get("city").equal("Ottawa"));

Example: A Subquery Expression Using a Not Exists Operation illustrates how to create an expression that matches all employees that have no projects.

A Subquery Expression Using a Not Exists Operation

    ExpressionBuilder emp = new ExpressionBuilder();
    ExpressionBuilder proj = new ExpressionBuilder();
    ReportQuery subQuery = new ReportQuery(Project.class, proj);
    subQuery.addAttribute("id");
    subQuery.setSelectionCriteria(proj.equal(emp.anyOf("projects"));
    Expression exp = emp.notExists(subQuery);