Scribd
Upload
6 views6 pages

SQL Recursive Queries Explained

The document discusses using recursive WITH statements in SQL to perform recursive queries. It provides examples of using recursion to find all ancestors of a person, calculate the total salary cost of employees working on a project including their managers, and find the cheapest flight route between two locations allowing connections. The document also notes recursion can introduce cycles that need to be handled.

Uploaded by

asim_ubit
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
6 views6 pages

SQL Recursive Queries Explained

The document discusses using recursive WITH statements in SQL to perform recursive queries. It provides examples of using recursion to find all ancestors of a person, calculate the total salary cost of employees working on a project including their managers, and find the cheapest flight route between two locations allowing connections. The document also notes recursion can introduce cycles that need to be handled.

Uploaded by

asim_ubit
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Recursion in SQL

Basic recursive WITH statement Demo


Jennifer Widom

SQL With Recursive Statement

Basic SQL Recursion

With Recursive R1 As (query-1), R2 As (query-2), ... Rn As (query-n) <query involving R1,,Rn (and other tables)>

Jennifer Widom

SQL With Recursive Statement


With Recursive R As ( base query Union recursive query ) <query involving R (and other tables)>

Basic SQL Recursion

Jennifer Widom

Example 1: Ancestors
ParentOf(parent,child)

Basic SQL Recursion

Find all of Marys ancestors

Jennifer Widom

Example 2: Company hierarchy


Employee(ID,salary) Manager(mID,eID) Project(name,mgrID)

Basic SQL Recursion

Find total salary cost of project X

Jennifer Widom

Example 3: Airline flights


Flight(orig,dest,airline,cost)

Basic SQL Recursion

Find cheapest way to fly from A to B Including what happens when we introduce cycles

Jennifer Widom

You might also like