Azure Synapse Recursive Query Alternative-Example

You can use recursive query to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy. Though Azure Synapse uses T-SQL, but it does not support all features that are supported in T-SQL. One of such features is Recursive CTE or VIEWS. Azure Synapse does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Azure Synapse Recursive Query Alternative with an working example.

Azure Synapse Recursive Query Alternative

In the other RDBMS such as Teradata or Snowflake, you can specify a recursive query by preceding a query with the WITH RECURSIVE clause or create a CREATE VIEW statement.

For example, following is the Teradata recursive query example.

WITH RECURSIVE temp_table (employee_number) AS
( SELECT root.employee_number
FROM employee root
WHERE root.manager_employee_number = 801
UNION ALL 
SELECT indirect.employee_number
FROM temp_table direct, employee indirect
WHERE direct.employee_number = indirect.manager_employee_number
)
SELECT * FROM temp_table ORDER BY employee_number;

You can notice WITH clause with RECURSIVE keyword.

Azure Synapse does not support recursive CTEs, if you know the depth of the hierarchy, then you can use T-SQL union all set operators or left outer join approach.

But, if you don’t know the depth of the recursive query hierarchy, you could do this with a good old-fashioned T-SQL loop.

Azure Synapse Recursive Query Alternative using T-SQL WHILE Loop

We will be using following employee table to implement the recursive query logic.

emp_id manager_id last_name first_name
------ ---------- --------- ----------
 801   NULL 	  mill      jhon
1016    801 	  miller    trace
1003    801 	  depp      jhony
1019    801 	  joker     jack
1010   1003 	  bieder    leo
1004   1003 	  samp      sam
1001   1003 	  miller    rock
1012   1004 	  kanch     jhon
1002   1004 	  hit       ralph
1015   1004 	  cutting   tom
1008   1019 	  cutting   sam
1006   1019 	  sharma    alok
1014   1019 	  lakh      ram
1011   1019 	  sunder    sham

Example, is borrowed from Teradata Recursive CTE example. Teradata Recursive Queries.

Following is the Synapse recursive query using T-SQL while loop.

-- Loop counter
DECLARE @counter INT = 1;

-- drop if temp table exists
IF OBJECT_ID('tempdb..#emp') IS NOT NULL DROP TABLE #emp;

-- Create temp table with initial records where employee directly reports
CREATE TABLE #emp
WITH
    (
    DISTRIBUTION = ROUND_ROBIN
    )
AS
WITH cte AS
(
SELECT 1 AS xlevel, *
FROM dbo.employee e
WHERE e.manager_employee_number = 801
)
SELECT *
FROM cte;

WHILE EXISTS (
SELECT c.*
FROM #emp p
INNER JOIN dbo.employee c ON p.employee_number = c.manager_employee_number
WHERE p.xlevel = @counter
)
BEGIN

-- Insert next level
INSERT INTO #emp ( xlevel, employee_number, manager_employee_number, last_name, first_name )
SELECT @counter + 1 AS xlevel, c.employee_number, c.manager_employee_number, c.last_name, c.first_name
FROM #emp p
INNER JOIN dbo.employee c ON p.employee_number = c.manager_employee_number
WHERE p.xlevel = @counter;
	
SET @counter += 1;

-- break in case infinite loop
IF @counter > 50
BEGIN 
	RAISERROR( 'Too many loops!', 16, 1 ) 
	BREAK 
END;
END

-- Display employee-manger hierarchy
SELECT  xlevel,employee_number as emp_id, manager_employee_number as manager_id, last_name, first_name  FROM #emp ORDER BY xlevel, manager_employee_number;

And here is the output of the query.

xlevel emp_id manager_id last_name first_name
------ ------ ---------- --------- ----------
   1   1016     801      miller    trace
   1   1003     801      depp      jhony
   1   1019     801      joker     jack
   2   1010    1003      bieder    leo
   2   1004    1003      samp      sam
   2   1001    1003      miller    rock
   2   1008    1019      cutting   sam
   2   1006    1019      sharma    alok
   2   1014    1019      lakh      ram
   2   1011    1019      sunder    sham
   3   1012    1004      kanch     jhon
   3   1002    1004      hit       ralph
   3   1015    1004      cutting   tom

Note that, we have used sqlcmd cli to execute queries.

Azure Synapse Recursive Query Alternative using LEFT OUTER JOIN

If you know the depth of the hierarchy, you can use left outer join to identify the hierarchy between employee and manager. You can check an example on Microsoft Q&A.

Synapse Recursive Query Alternative using UNION ALL

You can follow the example provided in my other article. Same query should work on Azure Synapse as well.