CODEWITHSUNDEEP
sqloracle-databasehierarchical-query
Javi Torre
Javi Torre

Reputation: 824

Oracle SQL start with/prior

I've successfully mananaged to understand how the connect by level works with the below example:

SELECT
    level,
    t.*
FROM
    (
        SELECT
            'a' AS col1,
            'b' AS col2
        FROM
            dual
        UNION ALL
        SELECT
            'c',
            'd'
        FROM
            dual
    ) t
CONNECT BY
    level <= 3

However, I'm struggling to understand the 'start with' and 'prior' concepts and what use cases do they have in real life. Could someone please walk me through using the provided example?

Upvotes: 3

Views: 6558

Answers (1)

MT0
MT0

Reputation: 168096

If you have a parent/child relationship:

CREATE TABLE t ( parent, child ) AS
  SELECT 'a', 'b' FROM dual UNION ALL
  SELECT 'b', 'c' FROM dual UNION ALL
  SELECT 'c', 'd' FROM dual UNION ALL
  SELECT 'd', 'e' FROM dual;

And you want to get the family tree starting from b and get all of the descendants then you can:

SELECT level,
       t.*
FROM   t
START WITH parent = 'b'
CONNECT BY PRIOR child = parent

Which outputs:

LEVEL | PARENT | CHILD
----: | :----- | :----
    1 | b      | c    
    2 | c      | d    
    3 | d      | e

Level 1 starts with b then level 2 has b's child c then level 3 has b's child's child (grandchild) d and they are all connected by the relationship that the PRIOR child is the (current) parent.

More examples of how to get different relationships can be found in this answer.

As an aside, your example in the question is a little confusing as it is finding all paths to a depth of 3 recursions. If you show the paths it has taken through the data using SYS_CONNECT_BY_PATH then you get a better idea:

SELECT level,
       t.*,
       SYS_CONNECT_BY_PATH( '('||col1||','||col2||')', '->' ) AS path
FROM (
  SELECT 'a' AS col1, 'b' AS col2 FROM dual UNION ALL
  SELECT 'c', 'd' FROM dual
) t
CONNECT BY level <= 3

Which outputs:

LEVEL | COL1 | COL2 | PATH                 
----: | :--- | :--- | :--------------------
    1 | a    | b    | ->(a,b)              
    2 | a    | b    | ->(a,b)->(a,b)       
    3 | a    | b    | ->(a,b)->(a,b)->(a,b)
    3 | c    | d    | ->(a,b)->(a,b)->(c,d)
    2 | c    | d    | ->(a,b)->(c,d)       
    3 | a    | b    | ->(a,b)->(c,d)->(a,b)
    3 | c    | d    | ->(a,b)->(c,d)->(c,d)
    1 | c    | d    | ->(c,d)              
    2 | a    | b    | ->(c,d)->(a,b)       
    3 | a    | b    | ->(c,d)->(a,b)->(a,b)
    3 | c    | d    | ->(c,d)->(a,b)->(c,d)
    2 | c    | d    | ->(c,d)->(c,d)       
    3 | a    | b    | ->(c,d)->(c,d)->(a,b)
    3 | c    | d    | ->(c,d)->(c,d)->(c,d)

You get 14 rows because you get 2 rows at level 1 (one for each combination of input row) and then 4 rows at level 2 (one for each input row for each level 1 row) and then 8 rows at level 2 (one for each input row for each level 2 row) and your output is growing exponentially.

db<>fiddle here

Upvotes: 2

Related Questions