# Correlated subqueries that reference other tables are not supported > BigQuery turns a correlated subquery into a join. When the subquery has a LIMIT, an ORDER BY or a window function it cannot, and the query fails. A join with ROW_NUMBER, QUALIFY or ARRAY_AGG says the same thing in a form it accepts. Source: https://docs.querylab.io/bigquery-errors/correlated-subqueries-that-reference-other-tables-are-not-supported/ Updated: 2026-11-04 ```text Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN. ``` ## What BigQuery means A correlated subquery uses a column of the outer query. It is easy to read as "run this once per outer row", but BigQuery does not run it that way. It rewrites the subquery as a join, and the rewrite only works for some shapes. When it cannot rewrite a subquery, it stops with this message. The message has no position and no names. It does not say which subquery failed, so you find it by looking for the shapes below. The error is raised when the query compiles, so a dry run reports it before anything is read or billed. Whether the rewrite works depends on what is in the subquery. In our tests a subquery that is a plain aggregate (`MAX`, `MIN`, `COUNT`, `ANY_VALUE`, `ARRAY_AGG`), with or without `GROUP BY` or `DISTINCT`, was accepted. These were not: - a `LIMIT`, even `LIMIT 1` on a `MAX` - an `ORDER BY` in an `ARRAY(...)` subquery, or inside `ARRAY_AGG` or `STRING_AGG` - a window function - a `UNION ALL` - a `HAVING` - another subquery inside the subquery that uses the outer column ## The smallest query that reproduces it The queries on this page use two small tables. They are real tables on purpose: the first-order query below compiled when the same data was written as inline `WITH` clauses, so a test built on a `WITH` clause may not reproduce this error. ```sql CREATE TABLE shop.customers AS SELECT * FROM UNNEST([STRUCT(10 AS customer_id, 'Ann' AS name), (11, 'Bo'), (12, 'Cy')]); CREATE TABLE shop.orders AS SELECT * FROM UNNEST([ STRUCT(1 AS order_id, 10 AS customer_id, 5 AS amount, DATE '2024-01-01' AS order_date), (2, 10, 7, DATE '2024-01-05'), (3, 11, 9, DATE '2024-01-09') ]); ``` The first order of each customer, written as a subquery: ```sql SELECT c.name, (SELECT o.amount FROM shop.orders o WHERE o.customer_id = c.customer_id ORDER BY o.order_date LIMIT 1) AS first_amount FROM shop.customers c ``` ```text Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN. ``` ## The fixes **Join, and keep one row per customer with `QUALIFY`.** `ROW_NUMBER()` does the job of `ORDER BY ... LIMIT 1`: ```sql SELECT c.name, o.amount AS first_amount FROM shop.customers c LEFT JOIN shop.orders o ON o.customer_id = c.customer_id QUALIFY ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY o.order_date) = 1 ORDER BY c.name ``` This returns `Ann, 5`, `Bo, 9` and `Cy, NULL`. Cy has no orders, and the `LEFT JOIN` keeps that customer with a `NULL` amount. See [QUALIFY](https://docs.querylab.io/bigquery/qualify/). **Join, and aggregate.** `ARRAY_AGG` with `ORDER BY` and `LIMIT` inside takes the first value per group: ```sql SELECT c.name, ARRAY_AGG(o.amount ORDER BY o.order_date LIMIT 1)[SAFE_OFFSET(0)] AS first_amount FROM shop.customers c LEFT JOIN shop.orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name ORDER BY c.name ``` The result is the same, with `NULL` for Cy. Group by `c.customer_id` as well as `c.name`: two customers with the same name would otherwise be merged into one row. Inside a correlated subquery, the same `ARRAY_AGG` is rejected. **A list per customer.** `ARRAY(SELECT ... WHERE o.customer_id = c.customer_id)` works. Add an `ORDER BY` inside and it fails. Move the ordering into a join. A customer with no orders makes the `LEFT JOIN` produce a `NULL` amount, and `ARRAY_AGG` keeps it in the array. BigQuery refuses to write an array with a `NULL` element to the result, so add `IGNORE NULLS`: ```sql SELECT c.name, ARRAY_AGG(o.amount IGNORE NULLS ORDER BY o.order_date) AS amounts FROM shop.customers c LEFT JOIN shop.orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name ORDER BY c.name ``` The amounts are `[5, 7]`, `[9]` and `NULL` for Cy, whose only value is `NULL`. Wrap the call in `IFNULL(..., [])` to get an empty array instead. Without `IGNORE NULLS` the same query fails for Cy with `Array cannot have a null element; error in writing field amounts`. **The largest order of each customer.** A window function over the join replaces a subquery that would need `ORDER BY` and `LIMIT`: ```sql SELECT c.name, o.order_id FROM shop.customers c JOIN shop.orders o ON o.customer_id = c.customer_id QUALIFY o.amount = MAX(o.amount) OVER (PARTITION BY c.customer_id) ORDER BY c.name ``` This returns `Ann, 2` and `Bo, 3`. If two orders of a customer tie for the largest amount, the condition `o.amount = MAX(...)` keeps both rows. Use `ROW_NUMBER()` as in the first fix when you want exactly one. **Keep the subquery when it is a plain aggregate.** A subquery that only needs `MAX`, `MIN` or `COUNT` is accepted, and it reads clearly: ```sql SELECT c.name, (SELECT MAX(o.amount) FROM shop.orders o WHERE o.customer_id = c.customer_id) AS biggest FROM shop.customers c ``` ## The variants of the message Two other messages come from the same rewrite. Each was produced by BigQuery: ```text Unsupported subquery with table in join predicate. LEFT SEMI JOIN cannot be used without a condition that is an equality of fields from both sides of the join. ``` **`Unsupported subquery with table in join predicate.`** A subquery in the `ON` clause of a join: ```sql SELECT c.name, o.order_id FROM shop.customers c JOIN shop.orders o ON o.customer_id = c.customer_id AND o.amount = (SELECT MAX(o2.amount) FROM shop.orders o2 WHERE o2.customer_id = c.customer_id) ``` Move the condition out of `ON`. The `QUALIFY` query above returns the same rows. **`LEFT SEMI JOIN cannot be used without a condition that is an equality...`** An `EXISTS` subquery whose condition has no equality between the outer and the inner table: ```sql SELECT c.name FROM shop.customers c WHERE EXISTS (SELECT 1 FROM shop.orders o WHERE o.amount * 2 > c.customer_id) ``` `EXISTS` works when it has the equality as well, such as `o.customer_id = c.customer_id AND o.amount > 6`. When there is nothing to match on, compare with an aggregate that does not depend on the outer row: ```sql SELECT c.name FROM shop.customers c WHERE c.customer_id < (SELECT MAX(o.amount * 2) FROM shop.orders o) ORDER BY c.name ``` This returns `Ann`, `Bo` and `Cy`. ## Queries that look wrong but run A correlated subquery that is a plain aggregate (the list near the top of the page) runs, and so do `IN` and `NOT EXISTS` with an equality. A condition that is not an equality is accepted inside a plain aggregate: `(SELECT SUM(o.amount) FROM shop.orders o WHERE o.order_id <= c.customer_id - 9)` ran and returned 5 for Ann, 12 for Bo and 21 for Cy. A subquery that returns more than one row passes the compile step and fails when it runs, with a different error. See [Scalar subquery produced more than one element](https://docs.querylab.io/bigquery-errors/scalar-subquery-produced-more-than-one-element/). ## Different errors that look like this one **`Unrecognized name: c`.** A subquery in `FROM` cannot use a column of the table before it: ```sql SELECT * FROM shop.customers c, (SELECT SUM(o.amount) AS total FROM shop.orders o WHERE o.customer_id = c.customer_id) x ``` ```text Unrecognized name: c at [2:96] ``` BigQuery's documentation says that a correlated join must reference an array in its right-hand `FROM` item. Join the two tables with a condition in `ON` instead. ## In Querylab.io The editor does not mark this error as you type. BigQuery's dry run reports it, and Querylab.io shows the message in the status bar when you pause typing. BigQuery gives no position, so the message is not tied to the subquery. Look in the subqueries of the query for the shapes listed near the top of the page. There is no one-click fix. For more on subqueries, see [Subqueries](https://docs.querylab.io/bigquery/subqueries/). ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Subqueries, correlated subqueries](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#correlated_subquery_concepts) - [Troubleshoot query issues](https://cloud.google.com/bigquery/docs/troubleshoot-queries)