Subqueries
A subquery can be used in quantified predicates, EXISTS predicate or IN predicate. A subquery should only specify a single element in the SELECT clause. When a path expression appears in a subquery, the identification variable of the path expression must be defined either in the subquery, in one of the containing subqueries, or in the outer query. A scalar subquery is a subquery that returns one value. A scalar subquery can be used in a basic predicate and in the SELECT clause of a dynamic query.
Example: Subqueries
SELECT OBJECT(e) FROM EmpBean e WHERE e.salary > ( SELECT AVG(e1.salary) FROM EmpBean e1)
The above query returns employees who earn more than average salary of all employees
SELECT OBJECT(e) FROM EmpBean e WHERE e.salary > ( SELECT AVG(e1.salary) FROM IN (e.dept.emps) e1 )
The above query returns employees who earn more than average salary of their department
SELECT OBJECT(e) FROM EmpBean e WHERE e.salary = ( SELECT MAX(e1.salary) FROM IN (e.dept.emps) e1 )
The above query returns employees who earn the most in their department
SELECT OBJECT(e) FROM EmpBean e WHERE e.salary > ( SELECT AVG(e.salary) FROM EmpBean e1 WHERE YEAR(e1.hireDate) = YEAR(e.hireDate) )
The above query returns employees who earn more than the average of employees hired in same year.