Recursion in SQL Basic recursive WITH statement Jennifer

Recursion in SQL Basic recursive WITH statement Jennifer Widom

SQL is not a “Turing complete” language Basic SQL Recursion § Simple, convenient, declarative § Expressive enough for most database queries § But basic SQL can’t express unbounded computations Jennifer Widom

Example 1: Ancestors Basic SQL Recursion Parent. Of(parent, child) Ø Find all of Mary’s ancestors Jennifer Widom

Example 2: Company hierarchy Basic SQL Recursion Employee(ID, salary) Manager(m. ID, e. ID) Project(name, mgr. ID) Ø Find total salary cost of project ‘X’ Jennifer Widom

Example 3: Airline flights Basic SQL Recursion Flight(orig, dest, airline, cost) Ø Find cheapest way to fly from ‘A’ to ‘B’ Jennifer Widom

SQL With Statement Basic SQL Recursion With R 1 As (query-1), R 2 As (query-2), . . . Rn As (query-n) <query involving R 1, …, Rn (and other tables)> Jennifer Widom

SQL With Statement Basic SQL Recursion With R 1(A 1, A 2, …, Am) As (query-1), R 2 As (query-2), . . . Rn As (query-n) <query involving R 1, …, Rn (and other tables)> Jennifer Widom

SQL With Recursive Statement Basic SQL Recursion With Recursive R 1 As (query-1), R 2 As (query-2), . . . Rn As (query-n) <query involving R 1, …, Rn (and other tables)> Jennifer Widom

SQL With Recursive Statement Basic SQL Recursion With Recursive R As ( base query Union recursive query ) <query involving R (and other tables)> Jennifer Widom