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

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.
Popular ebooks
A Developer's Guide to Building Resilient Cloud Applications with Azure by Hamida Rebai Trabelsi(9149)
Distributed Machine Learning with Python by Guanhua Wang(3538)
Getting Started with CockroachDB by Kishen Das Kondabagilu Rajanna(2570)
Exploratory Data Analysis with Python Cookbook by Ayodele Oluleye(1409)
Getting Started With CockroachDB: A Guide to Using a Modern, Cloud-Native, and Distributed SQL Database for Your Data-Intensive Apps by Kishen Das Kondabagilu. Rajanna(1166)
R Web Scraping Quick Start Guide by Olgun Aydin(1076)
PostgreSQL 13 Cookbook: Over 120 recipes to build high-performance and fault-tolerant PostgreSQL database solutions by Vallarapu Naga Avinash Kumar(1005)
Mastering PostgreSQL 15 - Fifth Edition by Hans-Jürgen Schönig(679)
Apache Hadoop 3 Quick Start Guide by Hrishikesh Karambelkar(440)
Pandas for Everyone: Python Data Analysis, 2nd Edition by Daniel Y. Chen(437)
Learn SQL with MySQL: Retrieve and Manipulate Data Using SQL Commands with Ease by Ashwin Pajankar(391)
SQL Query Design Patterns and Best Practices by Steve Hughes & Dennis Neer & Dr. Ram Babu Singh & Shabbir H. Mala & Leslie Andrews & Chi Zhang(383)
Deploy Node.js on GCP: A comprehensive guide to deploying Node.js on Google Cloud Platform by Jonathan Lin(370)
Configuring Sales and Distribution in SAP ERP by Unknown(348)
Leveling Up with SQL by Mark Simon(324)
Learning Data Science by Sam Lau(317)
Intermediate Python by Oswald Campesato(315)
The Definitive Guide to Data Integration by Pierre-Yves BONNEFOY Emeric CHAIZE Raphaël MANSUY Mehdi TAZI(295)
Data Engineering with AWS: A Comprehensive Guide to Building Robust Data Pipelines by Paul Brian(289)
Pandas Basics by Oswald Campesato(287)