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.
Practice this in a free lesson on learn.querylab.io: Top n per group with QUALIFY.
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, evenLIMIT 1on aMAX - an
ORDER BYin anARRAY(...)subquery, or insideARRAY_AGGorSTRING_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.
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:
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
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:
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.
Join, and aggregate. ARRAY_AGG with ORDER BY and LIMIT inside takes the first value per group:
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:
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:
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:
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:
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:
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:
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:
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.
Different errors that look like this one
Unrecognized name: c. A subquery in FROM cannot use a column of the table before it:
SELECT *
FROM shop.customers c, (SELECT SUM(o.amount) AS total FROM shop.orders o WHERE o.customer_id = c.customer_id) x
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.