Add a line to the SQL Expression docs that clearly states:
Correlated subqueries are not supported in file geodatabase SQL expressions
Details:
A database performs a correlated subquery when a nested subquery references a column from a table in a parent statement one or more levels above the subquery.
Since correlated subqueries aren’t supported, SQL expressions on file geodatabases can’t do things like get the greatest 1 per group, and other similar requirements:
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 The above file geodatabase SQL expression would select all rows in the table, which would be incorrect.
It’s important to note that the query fails silently, producing incorrect results, rather than throwing an error. Therefore, correlated subqueries should be avoided in file geodatabase SQL expressions to avoid producing misleading results.
Related:
