A correlated subquery is a subquery that depends on a value or table name from the main query that surrounds it. Source: Practical SQL by Anthony Debarros.
Example:
NOT EXISTS (
SELECT *
FROM road_insp r2
WHERE r2.asset_id = road_insp.asset_id AND r2.date_ > road_insp.date_
)
--Demo: https://dbfiddle.uk/gSGjLTCW If we use a correlated subquery in file geodatabase SQL, such as in an FGDB view or SQL expression, then ArcGIS Pro will execute the SQL without throwing an error, but the result will be incorrect. Despite the presence of the subquery in the WHERE clause, either all rows or no rows will be returned, which is not the correct result.
The incorrect results are misleading and can cause serious problems in our GIS analysis if not caught.
In a community post, an Esri staff member mentioned, "File Geodatabase does not support correlated subqueries." But you'd never know it when writing SQL in ArcGIS Pro.
Idea:
Could ArcGIS Pro be enhanced so that it raises an exception when a correlated subquery is used in FGDB SQL?
Related:
Thanks.
