Example: Ancestors
Example: Company hierarchy
Example: Airline flights
WITH R1 AS (query), R2 AS (query), ..., Rn AS (query) <query involving R1, R2,...,Rn and other relations>Idea:
WITH R1(A1, A2, ..., Am) AS (query), ...Example:
WITH Berk AS (SELECT ID, date FROM Apply WHERE location = "Berkeley"), SC AS (SELECT ID, date FROM Apply WHERE location = "SC") SELECT ID, Berk.date AS Bdate, SC.date AS SCdate FROM Berk, SC WHERE Berk.ID = SC.IDWITH statement looks like "temporary view definitions" for syntactic convenience.
WITH RECURSIVE Ancestor(anc,desc) AS ( (SELECT parent as anc, child as desc FROM ParentOf) UNION (SELECT Ancestor.anc, ParentOf.child as desc FROM Ancestor, ParentOf WHERE Ancestor.desc = ParentOf.parent) ) SELECT anc FROM Ancestor WHERE desc = "Mary"(show procedural evaluation with fixpoint)
Example: Total salary cost of Project "X"
WITH RECURSIVE Superior AS ( (SELECT * from Manager) UNION (SELECT S.mgrID, M.empID FROM Superior S, Manager M WHERE S.empID = M.mgrID) ) SELECT sum(salary) FROM Employee WHERE ID IN ((SELECT mgrID FROM Project WHERE name = 'X') UNION (SELECT empID FROM Project, Superior WHERE Project.name = 'X' AND Project.mgrID = Superior.mgrID))(show example data and computation)
Alternative formulation, more specific to this query:
WITH RECURSIVE Xemps(ID) AS ( (SELECT mgrID AS ID FROM Project WHERE name = "X") UNION (SELECT empID AS ID FROM Manager WHERE mgrID IN (Select ID FROM Xemps)) ) SELECT sum(salary) FROM Employee WHERE ID IN (SELECT ID FROM Xemps)(show computation)
Flights example left as exercise
Example: Nonlinear version of Ancestor:
WITH RECURSIVE Ancestor(anc,desc) AS ( (SELECT parent as anc, child as desc FROM ParentOf) UNION (SELECT A1.anc, A2.desc FROM Ancestor A1, Ancestor A2 WHERE A1.desc = A2.anc) ) SELECT anc FROM Ancestor WHERE desc = "Mary"
WITH RECURSIVE Banquet(ID) AS (SELECT ID FROM Sales WHERE amt > 10,000 AND ID NOT IN (SELECT ID FROM Bonus)), RECURSIVE Bonus(ID) AS (SELECT ID FROM Sales WHERE amt > 20,000 AND ID NOT IN (SELECT ID FROM Banquet)) <any query>Question: What's wrong?
Example: Given relation Q(x)
P(x) = Q(x) UNION R(x) R(x) = P(x) UNION sum(P(x))Question: What happens?