How to Select Using with Recursive Clause

How to select using WITH RECURSIVE clause

First of all, let us try to simplify and clarify algorithm description given on the manual page. To simplify it consider only union all in with recursive clause for now (and union later):

WITH RECURSIVE pseudo-entity-name(column-names) AS (
    Initial-SELECT
UNION ALL
    Recursive-SELECT using pseudo-entity-name
)
Outer-SELECT using pseudo-entity-name

To clarify it let us describe query execution process in pseudo code:

working-recordset = result of Initial-SELECT

append working-recordset to empty outer-recordset

while( working-recordset is not empty ) begin

    new working-recordset = result of Recursive-SELECT 
        taking previous working-recordset as pseudo-entity-name

    append working-recordset to outer-recordset

end

overall-result = result of Outer-SELECT 
    taking outer-recordset as pseudo-entity-name

Or even shorter - Database engine executes initial select, taking its result rows as working set. Then it repeatedly executes recursive select on the working set, each time replacing contents of the working set with query result obtained. This process ends when empty set is returned by recursive select. And all result rows given firstly by initial select and then by recursive select are gathered and feeded to outer select, which result becomes overall query result.

This query is calculating factorial of 3:

WITH RECURSIVE factorial(F,n) AS (
    SELECT 1 F, 3 n
UNION ALL
    SELECT F*n F, n-1 n from factorial where n>1
)
SELECT F from factorial where n=1

Initial select SELECT 1 F, 3 n gives us initial values: 3 for argument and 1 for function value.

Recursive select SELECT F*n F, n-1 n from factorial where n>1 states that every time we need to multiply last funcion value by last argument value and decrement argument value.

Database engine executes it like this:

First of all it executes initail select, which gives the initial state of working recordset:

F | n
--+--
1 | 3

Then it transforms working recordset with recursive query and obtain its second state:

F | n
--+--
3 | 2

Then third state:

F | n
--+--
6 | 1

In the third state there is no row which follows n>1 condition in recursive select, so forth working set is loop exits.

Outer recordset now holds all the rows, returned by initial and recursive select:

F | n
--+--
1 | 3
3 | 2
6 | 1

Outer select filters out all intermediate results from outer recordset, showing only final factorial value which becomes overall query result:

F 
--
6

And now let us consider table forest(id,parent_id,name):

id | parent_id | name
---+-----------+-----------------
1  |           | item 1
2  | 1         | subitem 1.1
3  | 1         | subitem 1.2
4  | 1         | subitem 1.3
5  | 3         | subsubitem 1.2.1
6  |           | item 2
7  | 6         | subitem 2.1
8  |           | item 3

'Expanding full tree' here means sorting tree items in human-readable depth-first order while calculating their levels and (maybe) paths. Both tasks (of correct sorting and calculating level or path) are not solvable in one (or even any constant number of) SELECT without using WITH RECURSIVE clause (or Oracle CONNECT BY clause, which is not supported by PostgreSQL). But this recursive query does the job (well, almost does, see the note below):

WITH RECURSIVE fulltree(id,parent_id,level,name,path) AS (
    SELECT id, parent_id, 1 as level, name, name||'' as path from forest where parent_id is null
UNION ALL
    SELECT t.id, t.parent_id, ft.level+1 as level, t.name, ft.path||' / '||t.name as path
    from forest t, fulltree ft where t.parent_id = ft.id
)
SELECT * from fulltree order by path

Database engine executes it like this:

Firstly, it executes initail select, which gives all highest level items (roots) from forest table:

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
1  |           | 1     | item 1           | item 1
8  |           | 1     | item 3           | item 3
6  |           | 1     | item 2           | item 2

Then, it executes recursive select, which gives all 2nd level items from forest table:

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
2  | 1         | 2     | subitem 1.1      | item 1 / subitem 1.1
3  | 1         | 2     | subitem 1.2      | item 1 / subitem 1.2
4  | 1         | 2     | subitem 1.3      | item 1 / subitem 1.3
7  | 6         | 2     | subitem 2.1      | item 2 / subitem 2.1

Then, it executes recursive select again, retrieving 3d level items:

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
5  | 3         | 3     | subsubitem 1.2.1 | item 1 / subitem 1.2 / subsubitem 1.2.1

And now it executes recursive select again, trying to retrieve 4th level items, but there are none of them, so the loop exits.

The outer SELECT sets the correct human-readable row order, sorting on path column:

id | parent_id | level | name             | path
---+-----------+-------+------------------+----------------------------------------
1  |           | 1     | item 1           | item 1
2  | 1         | 2     | subitem 1.1      | item 1 / subitem 1.1
3  | 1         | 2     | subitem 1.2      | item 1 / subitem 1.2
5  | 3         | 3     | subsubitem 1.2.1 | item 1 / subitem 1.2 / subsubitem 1.2.1
4  | 1         | 2     | subitem 1.3      | item 1 / subitem 1.3
6  |           | 1     | item 2           | item 2
7  | 6         | 2     | subitem 2.1      | item 2 / subitem 2.1
8  |           | 1     | item 3           | item 3

NOTE: Resulting row order will remain correct only while there are no punctuation characters collation-preceeding / in the item names. If we rename Item 2 in Item 1 *, it will break row order, standing between Item 1 and its descendants.

More stable solution is using tab character (E'\t') as path separator in query (which can be substituted by more readable path separator later: in outer select, before displaing to human or etc). Tab separated paths will retain correct order until there are tabs or control characters in the item names - which easily can be checked and ruled out without loss of usability.

It is very simple to modify last query to expand any arbitrary subtree - you need only to substitute condition parent_id is null with perent_id=1 (for example). Note that this query variant will return all levels and paths relative to Item 1.

And now about typical mistakes. The most notable typical mistake specific to recursive queries is defining ill stop conditions in recursive select, which results in infinite looping.

For example, if we omit where n>1 condition in factorial sample above, execution of recursive select will never give an empty set (because we have no condition to filter out single row) and looping will continue infinitely.

That is the most probable reason why some of your queries hang (the other non-specific but still possible reason is very ineffective select, which executes in finite but very long time).

There are not much RECURSIVE-specific querying guidlines to mention, as far as I know. But I would like to suggest (rather obvious) step by step recursive query building procedure.

• Separately build and debug your initial select.

• Wrap it with scaffolding WITH RECURSIVE construct
  
  and begin building and debugging your recursive select.

The recommended scuffolding construct is like this:

WITH RECURSIVE rec( <Your column names> ) AS (
    <Your ready and working initial SELECT>
UNION ALL
    <Recursive SELECT that you are debugging now>
)
SELECT * from rec limit 1000

This simplest outer select will output the whole outer recordset, which, as we know, contains all output rows from initial select and every execution of recusrive select in a loop in their original output order - just like in samples above! The limit 1000 part will prevent hanging, replacing it with oversized output in which you will be able to see the missed stop point.

• After debugging initial and recursive select build and debug your outer select.

And now the last thing to mention - the difference in using union instead of union all in with recursive clause. It introduces row uniqueness constraint which results in two extra lines in our execution pseudocode:

working-recordset = result of Initial-SELECT

discard duplicate rows from working-recordset /*union-specific*/

append working-recordset to empty outer-recordset

while( working-recordset is not empty ) begin

    new working-recordset = result of Recursive-SELECT 
        taking previous working-recordset as pseudo-entity-name

    discard duplicate rows and rows that have duplicates in outer-recordset 
        from working-recordset /*union-specific*/

    append working-recordset to outer-recordset

end

overall-result = result of Outer-SELECT 
    taking outer-recordset as pseudo-entity-name

WITH RECURSIVE SELECT via secondary table

I got home from work, and I just could not set this down!

But, out of that came a solution.

I highly recommend reading this answer about recursive queries to get a better idea of how they work, and what the syntax means. Quite brilliantly explained: How to select using WITH RECURSIVE clause

The Solution

WITH RECURSIVE all_fieldsets AS (
    SELECT * FROM fieldsets fs
        WHERE id = 59
    UNION ALL
    SELECT fs.* FROM fieldsets fs
        INNER JOIN all_fieldsets afs
        INNER JOIN fields f
            ON f.parent_fieldset_id = afs.id
            AND fs.parent_field_id = f.id
)
SELECT * FROM all_fieldsets

I had to use joins to get the information from the fields table, in order to get the next level in the hierarchy, and then do this recursively until there is an empty result in the recursive query.

SQL Server 2012 - Recursive CTE with two conditions under WHERE clause

This should do what you want:

with age_cte as (
    select 26 as wife_age, 28 as husband_age
    union all
    select
        case when wife_age < 120 then wife_age + 1 end,
        case when husband_age < 120 then husband_age + 1 end
    from age_cte
    where wife_age < 120 or husband_age < 120

) 
select * from age_cte;

That is:

• you want or in the where clause of the recursive query rather than and, so the query keeps going until both ages reach 120

• you can use conditional logic in the select to produce nulls when the age exceeds 120

Demo on DB Fiddle:

wife_age | husband_age
-------: | ----------:
      26 |          28
      27 |          29
      28 |          30
      29 |          31
...
     116 |         118
     117 |         119
     118 |         120
     119 |        null
     120 |        null

Recursive join to the same table using where clause from additional table

Look at this:

WITH RECURSIVE
cte AS ( 
    SELECT meal_id, mapped_ingredient_id ingredient_id
    FROM meals_to_ingredients
  UNION ALL
    SELECT cte.meal_id, iti.mapped_ingredient_id
    FROM cte
    JOIN ingredients_to_ingredients iti USING (ingredient_id)
)
SELECT cte.meal_id, ingredient_id, i.ingredient_name
FROM cte
JOIN ingredients i USING (ingredient_id)
-- WHERE cte.meal_id IN (1, 3)
ORDER BY 1,2

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=ee26da089c9bb2e5046fea0bc29041ff

SQL Recursive Queries , problems with understanding prior expression on connect by clause

As we know recursive queries are calling themselves and we always take over a parents column from the row before. With prior we are defining which column we want to take over from the row before in our recursive query. In my case I am always taking the arrivals column( ankunft) and change it to my new departure so I have to use ankunft as prior column. Otherwise the results will not be correct semantically, because we wanna simulate an airplane which is flying through the stations , beginning from a station we define (in my case Wien).

Thanks to The Impaler for trying to help me though.

How to implement a recursive with clause in larger sql statement

You can save the original name in the cte and query that:

WITH recursive_statement AS ( 
SELECT name as OriginalName, name, folder 
FROM table 

UNION ALL 

SELECT rs.OriginalName, t.name, t.folder 
FROM table t 
INNER JOIN recursive_statement rs 
ON t.name = rs.folder 
 ) 

SELECT * 
FROM recursive_statement
Where OriginalName = 'object1'

The recursion only happens when the cte is being queried, so performance shouldn't be affected.

---

Related Topics