Recursive Common Table Expressions
-
Greetings, I need to know how the recursive common table expression work behind the scene specifically when the recursive member returned with many result entries not a single result entry (i.e., single row) for example, the anchor member gets only one manager, and the first recursive member gets 3 employees follow that manager and they are, the 3 employees, in their turn are also managers to other employees etc. I hope that my question is clear
-
WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
(SELECT at.Account_ID,
at.Account_Code,
at.Account_Name1,
at.Account_Isleaf,
aib.AccountBranch_BranchID,
aib.AccountBranch_Natural,
aib.AccountBranch_Type,
ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue,
ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
CAST(NULL AS NVARCHAR(250)) AS [Entry],
N'TradingReceivableAccounts' AS [Description]
FROM AccountTree AS at INNER JOIN Account_InBranch AS aib
ON at.Account_ID = aib.AccountBranch_AccountID
AND aib.AccountBranch_Natural = 1
AND aib.AccountBranch_Type = 2
AND aib.AccountBranch_BranchID = 1UNION ALL
SELECT ac.Account_ID,
ac.Account_Code,
ac.Account_Name1,
ac.Account_Isleaf,
CAST(0 AS INT) AS AccountBranch,
CAST(0 AS BIT) AS NatureOfAccount,
CAST(0 AS TINYINT) AS AccountType,
jd.JournalDet_Debit,
jd.JournalDet_Credit,
CAST(jh.Journal_Code AS NVARCHAR(250)),
N'TradingReceivableAccounts' AS [Description]
FROM TradingReceivableAccounts AS CTE INNER JOIN AccountTree AS ac
ON CTE.AccountID = ac.Account_ID
INNER JOIN Journal_Details AS jd
ON jd.JournalDet_AccountID = ac.Account_ID
INNER JOIN Journal_Head AS jh
ON jh.Journal_ID = jd.JournalDet_HeadID
AND jh.Journal_BranchID = CTE.AccountBranch
AND jh.Journal_Date < N'02/18/2016')The above query have the same idea. The anchor member returns a specific types of financial accounts and the recursive member gets all the payments\receipts transactions associated with each account. For example, lets say the anchor query returns the accounts 1, 2, 3, and 4 and with each account returned the recursive member will return all the payments\receipts associated with the account. I need to know how it will work behind the scene.
-
WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
(SELECT at.Account_ID,
at.Account_Code,
at.Account_Name1,
at.Account_Isleaf,
aib.AccountBranch_BranchID,
aib.AccountBranch_Natural,
aib.AccountBranch_Type,
ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue,
ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
CAST(NULL AS NVARCHAR(250)) AS [Entry],
N'TradingReceivableAccounts' AS [Description]
FROM AccountTree AS at INNER JOIN Account_InBranch AS aib
ON at.Account_ID = aib.AccountBranch_AccountID
AND aib.AccountBranch_Natural = 1
AND aib.AccountBranch_Type = 2
AND aib.AccountBranch_BranchID = 1UNION ALL
SELECT ac.Account_ID,
ac.Account_Code,
ac.Account_Name1,
ac.Account_Isleaf,
CAST(0 AS INT) AS AccountBranch,
CAST(0 AS BIT) AS NatureOfAccount,
CAST(0 AS TINYINT) AS AccountType,
jd.JournalDet_Debit,
jd.JournalDet_Credit,
CAST(jh.Journal_Code AS NVARCHAR(250)),
N'TradingReceivableAccounts' AS [Description]
FROM TradingReceivableAccounts AS CTE INNER JOIN AccountTree AS ac
ON CTE.AccountID = ac.Account_ID
INNER JOIN Journal_Details AS jd
ON jd.JournalDet_AccountID = ac.Account_ID
INNER JOIN Journal_Head AS jh
ON jh.Journal_ID = jd.JournalDet_HeadID
AND jh.Journal_BranchID = CTE.AccountBranch
AND jh.Journal_Date < N'02/18/2016')The above query have the same idea. The anchor member returns a specific types of financial accounts and the recursive member gets all the payments\receipts transactions associated with each account. For example, lets say the anchor query returns the accounts 1, 2, 3, and 4 and with each account returned the recursive member will return all the payments\receipts associated with the account. I need to know how it will work behind the scene.
You have already largely described what will happen "behind the scenes". The Anchor member - i.e. the bit of the query after AS and before UNION ALL, is going to return a set of data in which you are interested. The Recursive member - i.e. the bit after the UNION ALL, keeps calling itself until it has used all of the data from the anchor. Recursion is usually used when there is a hierarchy or a sequence involved. Perhaps the simplest example I've seen is this SQL SERVER - Simple Example of Recursive CTE - Journey to SQL Authority with Pinal Dave[^] However, looking at your query and from your description it strikes me that all you need is a simple join rather than a recursive CTE. [EDIT] - I gave this some more thought and would like to offer this example of what is going on under the covers of an rCTE. Imagine a simple table (I'm using a cut down version of the table in the Northwind sample database)
use Sandbox
if exists (select * from sysobjects where id = object_id('dbo.Employees') )
drop table "dbo"."Employees"
GO
CREATE TABLE "Employees" (
"EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
"LastName" nvarchar (20) NOT NULL ,
"FirstName" nvarchar (10) NOT NULL ,
"Title" nvarchar (30) NULL ,
"ReportsTo" "int" NULL
)I've populated this with the same data as the sample database...
INSERT "Employees" VALUES('Davolio','Nancy','Sales Representative',2),
('Fuller','Andrew','Vice President, Sales',NULL),
('Leverling','Janet','Sales Representative',2),
('Peacock','Margaret','Sales Representative',2),
('Buchanan','Steven','Sales Manager',2),
('Suyama','Michael','Sales Representative',5),
('King','Robert','Sales Representative',5),
('Callahan','Laura','Inside Sales Coordinator',2),
('Dodsworth','Anne','Sales Representative',5)And created this rCTE query to traverse the hierarchy
;WITH Emp_CTE AS (
-- Anchor member - initialise the Level of recursion t -
You have already largely described what will happen "behind the scenes". The Anchor member - i.e. the bit of the query after AS and before UNION ALL, is going to return a set of data in which you are interested. The Recursive member - i.e. the bit after the UNION ALL, keeps calling itself until it has used all of the data from the anchor. Recursion is usually used when there is a hierarchy or a sequence involved. Perhaps the simplest example I've seen is this SQL SERVER - Simple Example of Recursive CTE - Journey to SQL Authority with Pinal Dave[^] However, looking at your query and from your description it strikes me that all you need is a simple join rather than a recursive CTE. [EDIT] - I gave this some more thought and would like to offer this example of what is going on under the covers of an rCTE. Imagine a simple table (I'm using a cut down version of the table in the Northwind sample database)
use Sandbox
if exists (select * from sysobjects where id = object_id('dbo.Employees') )
drop table "dbo"."Employees"
GO
CREATE TABLE "Employees" (
"EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
"LastName" nvarchar (20) NOT NULL ,
"FirstName" nvarchar (10) NOT NULL ,
"Title" nvarchar (30) NULL ,
"ReportsTo" "int" NULL
)I've populated this with the same data as the sample database...
INSERT "Employees" VALUES('Davolio','Nancy','Sales Representative',2),
('Fuller','Andrew','Vice President, Sales',NULL),
('Leverling','Janet','Sales Representative',2),
('Peacock','Margaret','Sales Representative',2),
('Buchanan','Steven','Sales Manager',2),
('Suyama','Michael','Sales Representative',5),
('King','Robert','Sales Representative',5),
('Callahan','Laura','Inside Sales Coordinator',2),
('Dodsworth','Anne','Sales Representative',5)And created this rCTE query to traverse the hierarchy
;WITH Emp_CTE AS (
-- Anchor member - initialise the Level of recursion tGreetings, I am so grateful for your interesting in answering me :) thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like: B reports to A C reports to B D reports to C E reports to D But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where C, D report to A E report to B F reports to C G, H report to D I, J report to E So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean. 1- A, B 2- C, D 3- F 4- G, H 5- E 6- I, J Is that how it will go?!
-
Greetings, I am so grateful for your interesting in answering me :) thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like: B reports to A C reports to B D reports to C E reports to D But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where C, D report to A E report to B F reports to C G, H report to D I, J report to E So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean. 1- A, B 2- C, D 3- F 4- G, H 5- E 6- I, J Is that how it will go?!
Depends on the database, Sql-server is breadth first, so it will return A,B,C,D,E,F,G,H,I,J. Oracle on the other hand can do both Breadth first or Depth first via a parameter, and in Depth first it would return A,C,F,D,G,H,B,E,I,J. How other databases handle things I don't know, but Breadth first is a good guess. <edit>You can quite easy get the result you want by adding a simple
order by recursionpathororder by level</edit>Wrong is evil and must be defeated. - Jeff Ello
-
Greetings, I am so grateful for your interesting in answering me :) thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like: B reports to A C reports to B D reports to C E reports to D But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where C, D report to A E report to B F reports to C G, H report to D I, J report to E So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean. 1- A, B 2- C, D 3- F 4- G, H 5- E 6- I, J Is that how it will go?!
This is really something that you need to observe for yourself. Try adding the following data to my sample table from my solution:
INSERT "Employees" VALUES('1Davolio','Nancy','Sales Representative',11),
('1Fuller','Andrew','Vice President, Sales',NULL),
('1Leverling','Janet','Sales Representative',11),
('1Peacock','Margaret','Sales Representative',12),
('1Buchanan','Steven','Sales Manager',11),
('1Suyama','Michael','Sales Representative',15),
('1King','Robert','Sales Representative',15),
('1Callahan','Laura','Inside Sales Coordinator',11),
('1Dodsworth','Anne','Sales Representative',15)Run the query unchanged and observe the results:
2 NULL Fuller Andrew Vice President, Sales 1 0/2
11 NULL 1Fuller Andrew Vice President, Sales 1 0/11
10 11 1Davolio Nancy Sales Representative 2 0/11/10
12 11 1Leverling Janet Sales Representative 2 0/11/12
14 11 1Buchanan Steven Sales Manager 2 0/11/14
17 11 1Callahan Laura Inside Sales Coordinator 2 0/11/17
13 12 1Peacock Margaret Sales Representative 3 0/11/12/13
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9 -
Compensating unfair downvote.
Wrong is evil and must be defeated. - Jeff Ello
-
Compensating unfair downvote.
Wrong is evil and must be defeated. - Jeff Ello
-
Sometimes I think people don't think. ;)
Wrong is evil and must be defeated. - Jeff Ello
-
Greetings, Thanks a lot and as you said indeed no need to use the recursive common table expression within the code I shared and I already changed it and I used the CROSS APPLY operator and it works 100% fine and in 0 time instead of 24 seconds :) it was my fault what I wrote firstly :). However, I've been curious on knowing how the recursive common table expression works behind the scene :) as I said I know how it works in its simplest form but I wanted to know what if the anchor member will return more than one result that will be joined with the recursive member and so on... how things will be done. Thanks for help now I knew what I need :). Best regards, Amr Mohammad Rashad
