The equivalent of 'connect by' in Oracle

_{Saturday, November 14, 2015 7:49 AM}

Hi All,

I've a table with hierarchical data relationship (parent-child) contained in one table.

How i can query the table and return the records sorted by its hierarchical level?

To those who are familiar with Oracle, i'm looking for the same thing that 'Connect by' does.

Thanks in advanced.

_{Saturday, November 14, 2015 10:27 AM ✅Answered}

During converting Oracle statements into SQL Server T-SQL, there seems no direct equivalent of Oracle PL/SQL CONNECT BY PRIOR ... START WITH statement in SQL Server. The SQL standard way to implement recursive queries, is the WITH clause (recursive CTE). 

Oracle PL/SQL example:

    select UserID, FirstName, LastName 
    from Users
    WHERE UserTypeID = 5
    CONNECT BY PRIOR UserID = ParentID  START WITH UserID = 2320 
    ORDER BY LastName

SQL Server T-SQL code:

    WITH N (Userid, FirstName, LastName, UserTypeID) 
    AS 
   (
    SELECT UserID, FirstName, LastName
    FROM Users
    WHERE Userid = 2320
    
    UNION ALL
    
    SELECT  np.UserID, np.FirstName, np.LastName, np.UserTypeID

    FROM Users AS np JOIN N ON N.UserID = np.ParentID
)

    SELECT Userid, FirstName, LastName

    FROM N
    WHERE UserTypeID = 5
    ORDER BY Lastname

Be careful with the CONNECT BY ... PRIOR statement, if in Oracle:

CONNECT BY UserID = PRIOR ParentID  START WITH UserID = 2320  instead of

CONNECT BY PRIOR UserID = ParentID  START WITH UserID = 2320  

Then in SQL should be:

    WITH N (Userid, FirstName, LastName, UserTypeID, ParentID) 
    AS 
   (
    SELECT UserID, FirstName, LastName, UserTypeID, ParentID
    FROM Users
    WHERE Userid = 2320
    
    UNION ALL
    
    SELECT  np.UserID, np.FirstName, np.LastName, np.UserTypeID, np.ParentID

    FROM Users AS np JOIN N ON np*.UserID = N.ParentID***
    )

    SELECT Userid, FirstName, LastName

    FROM N
    WHERE UserTypeID = 5
    ORDER BY Lastname

For more info about using recursive queries with WITH, please refer to here.

Please click "Mark As Answer" if my post helped.

_{Saturday, November 14, 2015 7:48 PM ✅Answered}

It is HIGHLY recommend avoiding using loops whenever you can! Using simple hierarchical data structure (self-join: using a column that stores the id of its father, while the father is on the same table) can be improved a lot by several difference architectures, which can fit your needs better.

In this approach each record stores the whole path to the root.

Let's start with the built-in option: SQL Server (from version 2008) includes a special type named hierarchyid especially for these cases. This is not the best solution but yet is much better then recursively getting the data, which mean that we are using loops. I would say that this is the simplest "OK solution", which fits most cases in small-medium databases.

You can read more about using hierarchyid in this link: https://msdn.microsoft.com/en-us/library/bb677173(v=sql.100).aspx

Implement hierarchy column structure yourself, in the way we used to do before2008 and in some cases after, then you can check this post: http://ariely.info/Blog/tabid/83/EntryId/135/Sorting-Parent-Childrens-data-as-an-hierarchical-tree.aspx

A bit more complex Solutions, which can improve reading the data dramatically (select query), on the expanse of reducing performance of DML query (delete/insert/update). These solutions can be very complex, and I advise you to search for several algorithms online before choosing the one that fit your need. One of the simples of these is using the Nested set model, which you can read more on Wikipedia: https://en.wikipedia.org/wiki/Nested_set_model

In these posts you have a simple implementation of Nested Intervals Model:

> https://www.infokarta.com/system/files/Recursive%20Hierarchies%20-%20No%20Columns.pdf > http://falsinsoft.blogspot.co.il/2013/01/tree-in-sql-database-nested-set-model.html > http://www.codeproject.com/Articles/4155/Improve-hierarchy-performance-using-nested-sets > http://blogs.msdn.com/b/mvpawardprogram/archive/2012/06/25/hierarchies-convert-adjacency-list-to-nested-sets.aspx

* In these solutions, DML queries need to do more than just working with the row that we want to change! This is the reason that DML queries reduced performance.

* Using range we have another issue regarding the INDEX, which is good to read about:

> http://sqlmag.com/database-development/optimization-tips-multiple-range-predicates-part-1
> http://sqlmag.com/t-sql/optimization-tips-multiple-range-predicates-part-2
>

I highly recommend to read this option as well: Nested Intervals with Farey Fractions
http://arxiv.org/html/cs/0401014
http://www.dbazine.com/oracle/or-articles/tropashko4/

>> If you do want to stay with your current database structure, then you need to use recursive query each time you need to get the data. In this case you can use the function in the post I mentioned above, or Dinesh's answer.

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]