SQL Server CROSS APPLY in Snowflake, Databricks and BigQuery
CROSS APPLY becomes a lateral join: LATERAL in Snowflake and Databricks. BigQuery only allows correlated joins against arrays, so the subquery is wrapped in UNNEST(ARRAY(SELECT AS STRUCT ...)).
SQL Server syntax: FROM left_table CROSS APPLY (subquery or table_valued_function) · SQL Server docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | FROM t, LATERAL (subquery) | The inline view can reference columns of tables to its left. | Snowflake docs |
| Databricks | Rewrite | FROM t JOIN LATERAL (subquery) | LATERAL subqueries can reference columns of preceding tables. | Databricks docs |
| BigQuery | Rewrite | FROM t JOIN UNNEST(ARRAY(SELECT AS STRUCT ... WHERE ... = t.col)) | Correlated joins must reference an array on the right side. | BigQuery docs |
Behavior differences to check
- OUTER APPLY keeps left rows without matches. Translate it to a LEFT JOIN on the lateral or UNNEST form and test that rows with no match survive.
- APPLY with TOP 1 ... ORDER BY inside (latest row per key) is often clearer as a window function: QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) = 1, which all three targets support.
Examples
Latest order per customer
SELECT c.id, o.order_id FROM customers c CROSS APPLY (SELECT TOP 1 order_id FROM orders WHERE orders.customer_id = c.id ORDER BY ordered_at DESC) o;SELECT c.id, o.order_id FROM customers c, LATERAL (SELECT order_id FROM orders WHERE orders.customer_id = c.id ORDER BY ordered_at DESC LIMIT 1) o;SELECT c.id, o.order_id FROM customers c JOIN LATERAL (SELECT order_id FROM orders WHERE orders.customer_id = c.id ORDER BY ordered_at DESC LIMIT 1) o;SELECT customer_id AS id, order_id FROM orders WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1;