ArcGIS Pro uses the SQLite SQL dialect for expressions on Excel files?

BB1234

Background:

It's interesting that ArcGIS Pro SQL expressions on Excel files use SQLite's SQL dialect, not the ArcGIS SQL dialect. I imagine one could do some pretty useful things with SQLite SQL, even though we're limited to the WHERE clause (as far as I know, we can't write full SQL queries on Excel files using SELECT, FROM, GROUP BY, etc.).

SQL reference for query expressions used in ArcGIS

Query expressions in ArcGIS adhere to standard SQL expressions. The SQL syntax you use within an expression differs depending on the data source. Each data source has its own variant of SQL, which are referred to as SQL dialects, such as the following:

...Mobile geodatabases, ST_geometry SQLite, GeoPackage, and Excel use SQLite SQL dialect.

Question:

How does ArcGIS Pro manage to use SQLite SQL on Excel files? I assume SQLite is not the native SQL dialect for Excel.

Does Pro copy the spreadsheet into a table in memory, and that allows ArcGIS to use SQLite queries on it?

BB1234

The SQL reference for query expressions used in ArcGIS docs also say:

Subqueries
A subquery is a query nested in another query and is supported by geodatabase data sources only.

I suppose that's not strictly true, since ArcGIS Pro uses SQLite SQL for Excel SQL expressions.

So I'm able to use a subquery (in this case, it's a correlated subquery) in Select by Attributes or a Definition Query on an Excel file. For example, for each ASSET_ID, get the road inspection row that has the latest DATE_. If there is a tie, use the row with the largest CONDITION value.

roadinsptable$.objectid IN (
       SELECT objectid
         FROM roadinsptable$ r2
        WHERE r2.asset_id = roadinsptable$.asset_id
     ORDER BY date_ DESC, condition DESC
        LIMIT 1
              )

Source:

• Join — Control what related record gets used
• Using a WHERE clause subquery, select the greatest n per group with a tie-breaker

It's strange that Excel spreadsheets (not a database) have more SQL support than file geodatabases in ArcGIS Pro. As we know, FGDBs don't support correlated subqueries.