SQL in 7 Days: A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-side Programming by Alex Bolenok

Author:Alex Bolenok [Bolenok, Alex]
Language: eng
Format: epub
Publisher: BPB Publications
Published: 2023-03-15T00:00:00+00:00

Query 126

chain_bottom | superior_or_himself | reports_to

--------------+---------------------+------------

1 | 1 | Ø

2 | 2 | 1

3 | 3 | 1

4 | 4 | 2

5 | 5 | 2

6 | 6 | 2

7 | 7 | 3

8 | 8 | 3

9 | 9 | 3

10 | 10 | 1

2 | 1 | Ø

3 | 1 | Ø

4 | 2 | 1

5 | 2 | 1

6 | 2 | 1

7 | 3 | 1

8 | 3 | 1

9 | 3 | 1

10 | 1 | Ø

4 | 1 | Ø

5 | 1 | Ø

6 | 1 | Ø

7 | 1 | Ø

8 | 1 | Ø

9 | 1 | Ø

What is happening here?

Elsie has added the keyword RECURSIVE to the definition of the CTE level. She has also moved the clause UNION ALL into the CTE.

A recursive CTE consists of two parts: the seed query (sometimes also called anchor query), and the recursive query. They need to be union compatible, and, in fact, they are added together using UNION ALL.

The recursive query needs to reference itself in an inner join15. This is what makes it recursive.

The seed query is run first. The resultset of the seed query is returned and remembered.

Then, the recursive query is run. Instead of level, the resultset of the seed query is substituted.

The resultset of the recursive query is, first, added to the final resultset, and second, substituted on the next iteration instead of level. The recursive part keeps being run over. As it is producing results, they are being added to the output, remembered, and used instead of the parameter level on the next invocation.

At some point, the recursive part yields no results, and its next invocation does not yield any results either. By recursion, it follows that no other invocation will ever produce any more results. The query stops.

This was not an easy journey. If you are still confused, look closer at Query 125 and Query 126. Note that they return the same results. This is because they do the same thing. Query 125, the non-recursive query, just spells out the logic explicitly.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.