A subquery is correlated when it uses a column of the query around it. The subquery then asks a question per row of the enclosing query, such as "does this employee have an order" or "how many orders does this employee have". Available from release 27.0. The column of the enclosing query is named through the alias of that query. A subquery first looks for a name among its own data sources. Only when it does not know the name there does it look at the query around it, and after that at the query around that one. A name that exists in both places therefore refers to the nearest one, so adding a column to a table cannot change the meaning of a subquery that already works. A correlation may reach outwards over any number of levels and may skip a level. A subquery two levels deep can use a column of the outermost query without the query in between playing any part. ## Supported Forms A correlated subquery is allowed in the same places as an uncorrelated one: - `exists` and `not exists`, which ask whether a matching row is present; - `in` and `not in`, which compare a value with the values the subquery returns; - a scalar subquery, which returns one value and may be used wherever an expression is allowed, so also in the select list, inside a calculation, inside a `case` and in the `order by` clause. A scalar subquery returns at most one row per row of the enclosing query. When more rows match, the statement stops with an error instead of returning a row twice. ## Examples Select the employees who work in a department that occurs in the second data set. The subquery uses `emp.dept`, a column of the enclosing query, so it is evaluated for every employee: ```sql select emp.code from ( select 'A100' code, 'SLS' dept union all select 'A200' code, 'ENG' dept ) emp where exists ( select 1 from ( select 'SLS' dept ) dpt where dpt.dept = emp.dept ) ``` This returns one row with the value `A100`. Employee `A200` works in a department that the second data set does not know. A scalar subquery in the select list counts the rows that belong to each employee: ```sql select emp.code , ( select count(*) from ( select 'A100' code union all select 'A100' code ) ord where ord.code = emp.code ) orders from ( select 'A100' code union all select 'A200' code ) emp ``` This returns `A100` with 2 and `A200` with 0. A `count` over no matching rows is zero. Every other aggregate, such as `max` or `sum`, returns the null value in that situation. A subquery may also be the term an `order by` sorts on. The employees are sorted by the budget of their department, largest first: ```sql select emp.code from ( select 'A100' code, 'SLS' dept union all select 'A200' code, 'ENG' dept ) emp order by ( select max(dpt.budget) from ( select 'SLS' dept, 300 budget union all select 'ENG' dept, 100 budget ) dpt where dpt.dept = emp.dept ) desc ``` This returns `A100` before `A200`, the budget of `SLS` being the larger of the two. The value the sort uses is not part of the result; only the columns of the select list are returned. ## Null Values A comparison with the null value has no outcome, and that outcome differs per form: - with `exists` a row that compares to the null value is not a match, so the subquery finds nothing; - with `not in` a single null value among the values returned makes the answer undecided, and the row is not returned. This also applies when the value being compared is itself null. ## Performance The engine rewrites a correlated subquery into a join before it runs the statement. Each data source is then read a fixed number of times, whatever the number of rows in the enclosing query. Without that rewrite the subquery would be run once per row, which on a platform reached over the internet means one request per row. Two consequences are worth knowing: - the number of requests to a platform does not grow along with the enclosing query, which matters most for platforms with a rate limit; - the subquery sees one state of the data for the whole statement. When another user changes data during the statement, the subquery does not see that change halfway through. A subquery run per row could see it. When the subquery compares one of its columns with a column of the enclosing query, the values of the enclosing query travel to the platform as a filter. The platform then returns the rows that can match instead of the whole table. ## Large Subqueries The rows of the subquery are held in memory while the statement runs. Above a million rows the engine no longer holds them all at once: it divides the rows over groups and reads the subquery once per group. The rows returned are the same either way, at the cost of reading the subquery a few times instead of once. The number of rows held at one time can be changed with the environment variable `INVANTIVE_SETTING_itgensql002`. A higher number uses more memory and fewer reads, a lower number the reverse. A statement whose subquery is divided over groups returns its rows grouped by that division. As with every query statement, the order of the rows is only fixed by an `order by` clause. ## Availability Correlated subqueries are available from release 27.0 for all platforms. In an `order by` clause, a column and a subquery may be sorted on; a calculation such as `order by price * quantity` is not yet supported.