Matt Velic

Recently I was asked about how I might show relationships between different entities within the same table. That one object might be the parent of another, and the child might have children or grandchildren of its own. To date, the person making the inquiry had been using a secondary table in order to work out these relationships, but he thought that there was probably a better way.

I replied, “You are absolutely right that there is a better way: you can use Recursive CTEs!”

The CTE, Recursed

To quote SQL Server Books Online, “A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.” So what makes a CTE recursive? It references itself over and over, building subsets until it has returned the whole results set. A bit of a warning: you can only use CTEs with SQL Server 2005 or later.

To start, let’s use TempDB and create some data so we can play. We’re creating a single table, dbo.Family, and it is self-referencing in that both Parents and Children are stored inside of it. You can tell the top-most Parent when a row doesn’t have an ID listed in the ParentID field. If it does, it means that the row is the child of another, and the ID listed is the Parent’s MainID. (I know, it’s not at all confusing.) Once you create the table and populate the data, you can run the SELECT statement and see the normal output.

USE tempdb;
GO

CREATE TABLE dbo.Family (
	MainID INT NOT NULL IDENTITY PRIMARY KEY
	,ParentID INT NULL
	,Name VARCHAR(35) NOT NULL
);

ALTER TABLE dbo.Family
ADD CONSTRAINT FK_ParentChild FOREIGN KEY (ParentID)
	REFERENCES dbo.Family (MainID);
GO

INSERT INTO dbo.Family (ParentID, Name)
VALUES (NULL,'Parent A')
,(1,'Child A')
,(2,'Grandchild A1')
,(2,'Grandchild A2')
,(NULL,'Parent B')
,(NULL,'Parent C')
,(5,'Child B1')
,(6,'Child C')
,(5,'Child B2')
,(8,'Grandchild C')
,(10,'Great-Grandchild C');
GO

SELECT MainID, ParentID, Name
FROM dbo.Family;

SELECT * FROM dbo.Family

The Boring Syntax Part

As you’ll see in the examples below, the CTE part of the query is the SELECT statement wrapped in parathesis and setting on top of the normal SELECT statement. This simple query must begin with the keyword WITH, and then you’d name your CTE. I’ve named mine reCTE. Another warning: because of the keyword WITH, all previous statements must be closed with a semi-colon (;). You may often see others write their CTEs as “;WITH reCTE” with the semi-colon preceding the keyword. It’s a safety measure as the whole query will fail if the previous statement hasn’t been closed yet. Additionally, I like to include explicit column names, though you don’t have to.

As for the CTE, it is your basic SELECT statement. It’s used to build the temporary set that the final SELECT statement can use; it’s similar to using a temp table. To make a CTE recursive, you simply add a UNION ALL. When using UNION ALL, the number of columns must be equal between the two sets, and they must be in the same order and share the same data type in order for the UNION to function properly.

As you’ll see in the example queries below, the first SELECT query is called the Anchor Member. It returns a valid table that’s used as the basis of the recursion. The query that follows the UNION ALL is termed the Recursive Member. It’s the query that is run against the anchor until the complete set has been returned.

Power of Recursion

As before, this functionality is available to users of SQL Server 2005 and later. What about the times before SQL 2005? Well, you had to rely on some seconday table in order to recurse through a results set. Using CTEs, you can encapsulate this within a single query. One common request is to see how “deep” a family is. This might come in the guise of managers to subordinates in displaying how flat (or deep) an organizational chart is.

WITH reCTE (MainID, ParentID, Name, Depth) AS (
	--Anchor Member
	SELECT f.MainID, f.ParentID, f.Name, 1 AS Depth
	FROM dbo.Family as f
	WHERE f.ParentID IS NULL
	UNION ALL
	--Recursive Member
	SELECT f.MainID, f.ParentID, f.Name, Depth + 1
	FROM dbo.Family as f
	INNER JOIN reCTE
		ON f.ParentID = reCTE.MainID
)
SELECT reCTE.MainID, reCTE.ParentID, reCTE.Name, reCTE.Depth
FROM reCTE
ORDER BY reCTE.Depth, reCTE.MainID;

How Low Can You Go?

As above, the Anchor Member is used to set the basic result. In this case, we’re querying for the absolute Parents in the set. The Recursive Member follows the UNION ALL, and we actually reference the CTE in a JOIN while still defining reCTE as a whole. This is what makes it recursive.

The outer query returns the set we’ve created with the CTE. For the above set, there are four levels of depth. This could translate to an organizational chart, or in discovering one’s secondary and tertiary managers.

Another common use for the recursive CTE is to find all relationships to a single Parent. With a little modification, we can use nearly the same query but aim for finding all the subordinates to Parent A by changing the WHERE clause in our Anchor to a specific MainID.

WITH reCTE (MainID, ParentID, Name) AS (
	--Anchor Member
	SELECT f.MainID, f.ParentID, f.Name
	FROM dbo.Family as f
	WHERE f.MainID = 1
	UNION ALL
	--Recursive Member
	SELECT f.MainID, f.ParentID, f.Name
	FROM dbo.Family as f
	INNER JOIN reCTE
		ON f.ParentID = reCTE.MainID
)
SELECT reCTE.MainID, reCTE.ParentID, reCTE.Name
FROM reCTE
ORDER BY reCTE.MainID;

Who is in this Family?

That’s all I’ve got on CTEs for now. If you want to learn even more, check out Audrey Hammond’s talk on the subject. It’s number 18 on the list, and it’ll require a PASS login, but it’s well worth it. She’s quite the awesome T-SQL Wizard.