SQL recursive query (SQL Server/Oracle recursive query) [syntax Difference Analysis]

After sqlserver2005, MSSQL began to use the recursive query method. Compare the methods for writing stored procedures or functions at the beginning. This method is simpler and more flexible.

Oracle also has its own tree structure recursive query method, connect

Next I will write a piece of SQL code by myself, and briefly comment out some usage of the CTE shared expression. Query the root node and subnode of the tree structure.

Code -- ----------------------------------------------------------------------
-- Author: jc_liumangtu ([DBA] Xiaoqi)
-- Date: 2010-03-30 15:09:42
-- Version:
-- Microsoft SQL Server 2005-9.00.1399.06 (Intel x86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (build 2600: Service Pack 3)
--
-- ----------------------------------------------------------------------
Use Test
Set Nocount On
If Object_id ( ' Dept ' , ' U ' ) Is Not Null
Drop Table Dept
Go
Create Table Dept (ID Int , Parentid Int , Name Varchar ( 20 ))
Insert Into Dept Select 1 , 0 , ' AA '
Insert Into Dept Select 2 , 1 , ' Bb '
Insert Into Dept Select 3 , 1 , ' CC '
Insert Into Dept Select 4 , 2 , ' Dd '
Insert Into Dept Select 5 , 3 , ' EE '
Insert Into Dept Select 6 , 0 , ' FF '
Insert Into Dept Select 7 , 6 , ' Gg '
Insert Into Dept Select 8 , 7 , ' HH '
Insert Into Dept Select 9 , 7 , ' II '
Insert Into Dept Select 10 , 7 , ' Jj '
Insert Into Dept Select 11 , 9 , ' Kk '

Go
Select * FromDept;

-- Query all the upper-level root nodes of a node in the tree structure.
With Cte_root (ID, parentid, name)
As
(
-- Start Condition
Select ID, parentid, name
From Dept
Where Name = ' II ' -- List subnode query Conditions
Union All
-- Recursive Condition
Select A. ID, A. parentid, A. Name
From Dept
Inner Join
Cte_root B -- Execute recursion. here we need to understand it.
On A. ID = B. parentid -- Query the subnode (A. ID) based on the basic table conditions and find its parent node (B. parentid) recursively through CTE ).
) -- You can compare it with cte_child of the subnode.
Select * From Cte_root;

-- Query all subnodes under a node in the tree structure.
With Cte_child (ID, parentid, name)
As
(
-- Start Condition
Select ID, parentid, name
From Dept
Where Name = ' II ' -- List parent node query Conditions
Union All
-- Recursive Condition
Select A. ID, A. parentid, A. Name
From Dept
Inner Join
Cte_child B
On (A. parentid = B. ID) -- Based on the queried parent node (A. Parent), recursively query its child nodes (B. ID) through CTE)
)

Select * FromCte_child--You can change the previous query condition 'II' and then test the result.

Id parentid name
-- ----------------------------------------
1 0 AA
2 1 Bb
3 1 CC
4 2 Dd
5 3 EE
6 0 FF
7 6 Gg
8 7 HH
9 7 II
10 7 Jj
11 9 Kk

ID parentid name
-- --------- --------------------------
9 7 II
7 6 GG
6 0 FF

Id parentid name
------------------------------------------
97II
119Kk
CopyCode

Some limitations of CTE are introduced in msdn:

There must be at least one positioning point member and one recursive member. Of course, you can define multiple positioning point Members and recursive members, but all positioning point Members must be prior to the recursive member.
The Union all, union, intersect, and except T operators must be used between the anchor members. The Union all must be used between the last anchor member and the recursive member, and the Union all must be used between recursive members.
The number and type of fields in the anchor and recursive members must be exactly the same.
The from clause of recursive members can only reference the CTE object once.
The following items cannot appear in recursive members:
Select distinct
Group
Having
Scalar Aggregation
Top
Left, right, and outer join (inner join is allowed)
Subquery

Next we will introduce the recursive query method in Oracle, connect by prior, start. Compared with SQL Server, the Oracle method is simpler and easier to understand. It is easy to understand its usage. I will use the same data and structure as SQL Server to demonstrate code and describe the usage of some keywords.

Select .....

Connect by {prior column name 1 = column name 2 | column name 1 = prior column name 2}
[Start with];

The following is a code test:

Code -- Create a table
Create Table Dept (ID Int , Parentid Int , Name Varchar ( 20 ));
-- Add test data, which is the same as the preceding SQL server data.
Insert Into Dept Select 1 , 0 , ' AA ' From Dual;
Insert Into Dept Select 2 , 1 , ' Bb ' From Dual;
Insert Into Dept Select 3 , 1 , ' CC ' From Dual;
Insert Into Dept Select 4 , 2 , ' Dd ' From Dual;
Insert Into Dept Select 5 , 3 , ' EE ' From Dual;
Insert Into Dept Select 6 , 0 , ' FF ' From Dual;
Insert Into Dept Select 7 , 6 , ' Gg ' From Dual;
Insert Into Dept Select 8 , 7 , ' HH ' From Dual;
Insert Into Dept Select 9 , 7 , ' II ' From Dual;
Insert Into Dept Select 10 , 7 , ' Jj ' From Dual;
Insert Into Dept Select 11 , 9 , ' Kk ' From Dual;
Commit ;

-- Query the root node (parent node)
Select * From Dept -- Query basic tables
Connect By ID = Prior parentid -- Connect by is the keyword associated with the field. Prior has the prefix and prefix meaning, which is the first field and the last layer of recursion.
Start With Name = ' II ' ; -- Start with is the starting position of recursion. You can also use ID or parentid. You can modify the value of II to test other data.

-- Query Result
Id parentid name
9 7 II
7 6 Gg
6 0 FF

--Query subnodes

Select * FromDept
ConnectByPrior ID=Parentid--In the same statement, only the prior seat is changed, and the direction is changed. Here, ID is the first layer of recursion.
StartWithName='II';

--Query Result
Id parentid name
97II
119Kk

--The test results are consistent with those of SQL Server, but the statements are more refined and concise.Copy code

After testing sqlserver and Oracle respectively, we found that both databases support recursive queries. In contrast, Oracle's recursive query statements are more concise and easy to understand.

During the test, sqlserver is more convenient to generate test data. The above code can be copied and executed repeatedly, while the Oracle copy can be executed once and repeatedly, when you create a table, an error is reported. The reason is very simple. It is very troublesome to use code to implement Oracle to determine whether the table exists and then delete and recreate it. Sqlserver only needs to drop the table after if and then create it. Therefore, the two databases have their own merits.