SQL Server Error Messages - Msg 462¶
Error Message¶
Server: Msg 462, Level 16, State 1, Line 1
Outer join is not allowed in the recursive part of a recursive common table expression '<Common Table Expression Name>'.
Causes¶
A common table expression (CTE), introduced in SQL Server 2005, can be thought of as a temporary result set that is defined with the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE that references itself is referred to as a recursive common table expression.
The general syntax of a recursive common table expression is as follows:
WITH <CTE Name> [ ( [, …n] ) ]
AS (
<CTE Query Definition – Anchor Member>
UNION ALL
<CTE Query Definition – Recursive Member>
)
SELECT * FROM <CTE Name>
Given this general syntax of a recursive common table expression, recursive CTE definition must contain at least two CTE query definition, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definition must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
One of the restrictions when defining a recursive common-table expression is that the LEFT OUTER JOIN or RIGHT OUTER JOIN is not allowed in the recursive member.
To illustrate on how this error message may be encountered, the following table structure is used:
CREATE TABLE [dbo].[Folder] (
[FolderID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FolderName] VARCHAR(100) NOT NULL,
[ParentFolderID] INT NULL REFERENCES [dbo].[Folder]( [FolderID] )
)
CREATE TABLE [dbo].[File] (
[FileID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FolderID] INT NOT NULL REFERENCES [dbo].[Folder] ( [FolderID] ),
[FileName] VARCHAR(100)
)
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Inetpub' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Program Files' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Windows' )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'wwwroot', 1 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft Office', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'assembly', 3 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 )
INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 4, 'iisstart.htm')
INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 8, 'calc.exe')
INSERT INTO [dbo].[File] ( [FolderID], [FileName] )
VALUES ( 8, 'notepad.exe')
The following recursive common-table expression (CTE) tries to return all folders defined in the [dbo].[Folder] table together with any files that belong to that folder. If no files exist for a folder, the details of that folder should still be returned by the query with a blank value as the file name.
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName],
[FullFolderPath], [FileName] ) AS (
SELECT A.[FolderID], A.[FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + A.[FolderName] AS VARCHAR(MAX)), B.[FileName]
FROM [dbo].[Folder] A LEFT OUTER JOIN [dbo].[File] B
ON A.[FolderID] = B.[FolderID]
WHERE A.[ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName],
ISNULL(B.[FileName], '') AS [FileName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
LEFT OUTER JOIN [dbo].[File] C
ON A.[FolderID] = C.[FolderID]
)
SELECT [ParentFolderName], [FullFolderPath], [FileName]
FROM FullFolder
ORDER BY [ParentFolderName], [FullFolderPath], [FileName]
But since the recursive member of this CTE used an LEFT OUTER JOIN, the following error message is generated:
Msg 462, Level 16, State 1, Line 2
Outer join is not allowed in the recursive part of a recursive common table expression 'FullFolder'.
Solution / Work Around¶
There are a couple of ways of avoiding this error message and still be able to accomplish the result needed from a recursive CTE. Changing the LEFT OUTER JOIN to an INNER JOIN will not work. Although no error will be generated, the desired result will be different. Using an INNER JOIN, if there are no files associated with a folder, that folder will not be returned by the query.
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath], [FileName] ) AS (
SELECT A.[FolderID], A.[FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + A.[FolderName] AS VARCHAR(MAX)), B.[FileName]
FROM [dbo].[Folder] A INNER JOIN [dbo].[File] B
ON A.[FolderID] = B.[FolderID]
WHERE A.[ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName],
ISNULL(C.[FileName], '') AS [FileName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
INNER JOIN [dbo].[File] C
ON A.[FolderID] = C.[FolderID]
)
SELECT [FolderName], [FullFolderPath], ISNULL([FileName], '') AS [FileName]
FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
In the sample data provided above, there will be no output returned by this query since there are no files associated with any of the folder in the anchor member.
One way of overcoming this error message and still be able to generate the desired output is to do the LEFT OUTER JOIN outside the recursive common table expression, as can be seen from the following query:
-- Option #1 : Do the LEFT OUTER JOIN Outside the Recursive CTE
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + [FolderName] AS VARCHAR(MAX))
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT A.[FolderName], A.[FullFolderPath], ISNULL(B.[FileName], '') AS [FileName]
FROM FullFolder A LEFT OUTER JOIN [dbo].[File] B
ON A.[FolderID] = B.[FolderID]
ORDER BY A.[ParentFolderName], A.[FullFolderPath], B.[FileName]
Executing this updated query will generate the following result using the sample data:
FolderName FullFolderPath FileName
-------------------- ------------------------------------ ----------------
Inetpub \Inetpub
Program Files \Program Files
Windows \Windows
Wwwroot \Inetpub\wwwroot iisstart.htm
Microsoft Office \Program Files\Microsoft Office
Microsoft SQL Server \Program Files\Microsoft SQL Server
Assembly \Windows\assembly
system32 \Windows\system32 calc.exe
system32 \Windows\system32 notepad.exe
Another way of overcoming this error message while still producing the same desired result is with the use of the OUTER APPLY operator together with a table-valued function. The APPLY operator allows the invocation of a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
The first step is to create a table-valued function that accepts as parameter the folder ID and returns the file names that belong to that folder:
CREATE FUNCTION [dbo].[ufn_GetFolderFiles] ( @FolderID INT )
RETURNS TABLE
AS
RETURN (SELECT [FileName]
FROM [dbo].[File]
WHERE [FolderID] = @FolderID)
GO
Given this table-valued function, the OUTER APPLY operator is used within the recursive member of the recursive CTE to return the desired output, as can be seen in the following script:
-- Option #2: Use OUTER APPLY With a Table-Valued Function
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath], [FileName] ) AS (
SELECT A.[FolderID], A.[FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + A.[FolderName] AS VARCHAR(MAX)), B.[FileName]
FROM [dbo].[Folder] A OUTER APPLY [dbo].[ufn_GetFolderFiles] ( A.[FolderID] ) B
WHERE A.[ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName],
ISNULL(C.[FileName], '') AS [FileName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
OUTER APPLY [dbo].[ufn_GetFolderFiles] ( A.[FolderID] ) C
)
SELECT [FolderName], [FullFolderPath], ISNULL([FileName], '') AS [FileName]
FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
The output of this updated script is as follows, which is the same as the output earlier:
FolderName FullFolderPath FileName
-------------------- ------------------------------------ ----------------
Inetpub \Inetpub
Program Files \Program Files
Windows \Windows
Wwwroot \Inetpub\wwwroot iisstart.htm
Microsoft Office \Program Files\Microsoft Office
Microsoft SQL Server \Program Files\Microsoft SQL Server
Assembly \Windows\assembly
system32 \Windows\system32 calc.exe
system32 \Windows\system32 notepad.exe