Oracle CONNECT BY in Snowflake, Databricks and BigQuery
Snowflake supports CONNECT BY with START WITH and PRIOR. Databricks (SQL and Runtime 17.0+) and BigQuery need a recursive common table expression instead.
Oracle syntax: SELECT ... START WITH condition CONNECT BY [NOCYCLE] PRIOR parent = child · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Same name | START WITH ... CONNECT BY PRIOR ... | NOCYCLE, CONNECT_BY_ISCYCLE and CONNECT_BY_ISLEAF are not supported. SYS_CONNECT_BY_PATH is. | Snowflake docs |
| Databricks | Rewrite | WITH RECURSIVE cte AS (anchor UNION ALL recursive_step) | Databricks SQL and Databricks Runtime 17.0 and later; recursion stops at 100 levels unless MAX RECURSION LEVEL is set. | Databricks docs |
| BigQuery | Rewrite | WITH RECURSIVE cte AS (anchor UNION ALL recursive_step) | LEVEL and the path are computed as columns of the CTE. | BigQuery docs |
Behavior differences to check
Snowflake
- Queries using NOCYCLE need cycle-free data or a rewrite as a recursive CTE with an explicit path check.
Databricks
- LEVEL, SYS_CONNECT_BY_PATH and ORDER SIBLINGS BY have no direct form; carry a level counter and a path string in the CTE and order by the path.
BigQuery
- LEVEL, SYS_CONNECT_BY_PATH and ORDER SIBLINGS BY have no direct form; carry a level counter and a path string in the CTE and order by the path.
Examples
Org chart from the CEO down
SELECT emp_id, manager_id, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id;SELECT emp_id, manager_id, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id;WITH RECURSIVE org AS (SELECT emp_id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id) SELECT * FROM org;WITH RECURSIVE org AS (SELECT emp_id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id) SELECT * FROM org;