With RECURSIVE and MySQL

With RECURSIVE and MySQL

If you have been using certain DBMSs, or reading recent versions of the "SQL standard", you are probably aware of the so-cal LED "with clause" of SQL. Some call it subquery factoring. Others call it Common Table Expression. A form of the with CLAUSE, 'withRECURSIVE ', allows to design a RECURSIVE query:a query which repeats I Tself again and again, each time using the results of the previous iteration. This can is quite useful to produce reports based on hierarchical data. And thus is a alternative to Oracle's CONNECT by. MySQL does not natively-RECURSIVE, but it's easy-to-emulate it with a generic, reusable stored procedure. Read the full article here ...

Http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html

If you have been using certain DBMSs, or reading recent versions of the "SQL standard", you are probably aware of the so-cal LED "with clause" of SQL. Some call it subquery factoring. Others call it Common Table Expression. In it simplest form, this feature is a kind of "boosted derived table".
Assume that a table T1 have three columns:

CREATE TABLE T1 (year INT, # 2000, 2001, 2002 ... MONTH INT, # January, February, ... SALES INT # How much we sold on the that month of this year);

Now I want to know the sales trend (increase/decrease), year following year:

SELECT D1. Year, D1.s>d2. s then ' increase ' ELSE ' decrease ' END] as Trendfrom  (SELECT year, SUM (SALES) as S from T1 GROUP by year) as D1,  (s Elect year, SUM (SALES) as-S from T1 GROUP by year) as D2where D1. Year = D2. YEAR-1;

Both derived tables was based on the same subquery text, but usually a DBMS was not smart enough to recognize it. Thus, it'll evaluate "select year, SUM (SALES) ... GROUP by year "twice! A first time to fill D1, a second time to fill D2. This limitation is sometimes stated as "It's not possible to refer to a derived table twice in the same query". Such double evaluation can leads to a serious performance problem. Using with, this limitation does not exist, and the following statement evaluates the subquery only once:

With D as (select Year, SUM (SALES) as S from T1 GROUP by year) SELECT D1. Year, D1.s>d2. S then ' increase ' ELSE ' decrease ' END) as Trendfrom D as D1, D as D2where D1. Year = D2. YEAR-1;

This already demonstrates one benefit of. In MySQL, with was not yet supported. But it can is emulated with a view:

 create VIEW D as (SELECT year, SUM (SALES) as S from T1 GROUP by year); SELECT D1. Year, D1.s>d2. S then ' increase ' ELSE ' decrease ' END) as Trendfrom D as D1, D as D2where D1. Year = D2. Year-1;drop VIEW D;  

Instead of a view, I could as well create D as a normal table. But isn't as a temporary table, because in MySQL a temporary table cannot is referred twice in the same query, as mentioned In the manual.
After this short introduction, showing the simplest form of with, I would like to turn to the more complex form O F with:the RECURSIVE form . According to the ' SQL standard ' to use the ' recursive form ', you should write with recursive. However, looking at some other DBMSs, they seem to not require the RECURSIVE word. With RECURSIVE is a powerful construct. For example, it can do the same job as Oracle's CONNECT by clause (you can check out some example conversions between both Constructs). Let's walk through an example, to understand, and RECURSIVE does.
assume you has a table of employees (this was a very classical example of with RECURSIVE):

CREATE TABLE EMPLOYEES (ID INT PRIMARY key,name VARCHAR), manager_id Int,index (manager_id), FOREIGN KEY (manager_id) RE Ferences EMPLOYEES (ID)); INSERT into EMPLOYEES VALUES (333, "Yasmina", NULL), (198, "John", 333), ("Pedro", 198), (4610, " Sarah "," (692, "Pierre", "Tarek", 333);

In the other words, Yasmina is CEO, John and Tarek. Pedro reports to John, Sarah and Pierre report to Pedro. In a big company, they would is thousands of rows in this table.
Now, let's say that's would like to know, for each employee: "How many people is, directly and indirectly, reporting to Him/Her "? Here's how I would do it. First, I would make a list of people who is not managers:with a subquery I get the list of all managers, and using N OT in (subquery) I get the list of all non-managers:

Select ID, NAME, manager_id, 0 as Reportsfrom Employeeswhere ID not in (SELECT manager_id from EMPLOYEES WHERE manager_id is not NULL);

Then I would insert the results into a new table named employees_extended; EXTENDED stands for ' EXTENDED with more information ', the new information being the fourth column named Reports:it is a C Ount of people who is reporting directly or indirectly to the employee. Because We have listed people who is not managers, they has a value of 0 in the REPORTS column. Then, we can produce the rows for "first level" managers (the direct managers of Non-managers):

SELECT m.id, M.name, m.manager_id, SUM (1+e.reports) as Reportsfrom EMPLOYEES M JOIN employees_extended E on M.id=e.manager _idgroup by M.id, M.name, m.manager_id;

Explanation:for a row of M (that's, for-an employee), the JOIN would produce zero or more rows, one per Non-manager Direc tly reporting to the employee. Each such non-manager contributes to the value of REPORTS for his manager, through and Numbers:1 (the Non-manager himself ), and the number of Direct/indirect reports of the Non-manager (i.e. the value of reports for the Non-manager). Then I would empty employees_extended, and fill it with the rows produced just above, which describe the first level Manag ERs. Then the same query should is run again, and it would produce information about the ' second level ' managers. And so on. Finally, at one point Yasmina would be the only row of employees_extended, and when we run the above SELECT again, the JOIN Would produce no rows, because e.manager_id would be NULL (she's the CEO). We are done.
It's time for a recap:employees_extended have been a kind of "temporary buffer", which hassuccessivelyHeld non-managers, first level managers, second level managers, etc. We have usedrecursion. The answer to the original problem is:theUnionOf all the successive content of employees_extended. Non-managers has been the start of the recursion, which is usually called "the anchor member" or "the Seed". The select query which moves from a step of recursion to the next one, is the "recursive member". The complete statement looks like this:

With recursive# the temporary buffer, also used as UNION Result:employees_extendedas (  # The seed:  SELECT ID, NAME,  manager_id, 0 as REPORTS from  EMPLOYEES  where ID not in (SELECT manager_id from EMPLOYEES WHERE manager_id are not NULL) UNION All  # The recursive member:  SELECT m.id, M.name, m.manager_id, SUM (1+e.reports) as REPORTS from  EM  Ployees M JOIN employees_extended E on m.id=e.manager_id  GROUP by M.id, M.name, m.manager_id) # What are we want to does with The complete result (the UNION): SELECT * from employees_extended;

MySQL does not yet-RECURSIVE, but it's possible to code a generic stored procedure which can easily emulate It. Would call it:

Call With_emulator ("employees_extended", "  Select ID, NAME, manager_id, 0 as REPORTS from  EMPLOYEES  WHERE ID Not in (select manager_id from EMPLOYEES WHERE manager_id are not NULL) ","  select M.id, M.name, m.manager_id, SUM (1+E.R Eports) as REPORTS from  EMPLOYEES M joins employees_extended E on m.id=e.manager_id  GROUP by M.id, M.name, M.manage r_id "," SELECT * from employees_extended ", 0," ");

You can recognize, as arguments of the stored procedure, every member of the A with standard syntax:name of the temporary b Uffer, query for the seed, a query for the recursive member, and a what-does with the complete result. The last of the arguments-0 and the empty string-are details which you can ignore for now.
Here are the result returned by this stored procedure:

 +------+---------+------------+---------+| ID | NAME | manager_id |   REPORTS |+------+---------+------------+---------+| 72 |         Pierre |       29 |  0 | | 692 |        Tarek |       333 | 0 | | 4610 |         Sarah |       29 |   0 | | 29 |        Pedro |       198 |  2 | | 333 |       Yasmina |       NULL |  1 | | 198 |        John |       333 |  3 | | 333 |       Yasmina |       NULL | 4 |+------+---------+------------+---------+7 rows in set  

Notice how Pierre, Tarek and Sarah had zero reports, Pedro has both, which looks correct ... However, Yasmina appears in and rows! Odd? Yes and No. Our algorithm starts from Non-managers, the "leaves" of the tree (Yasmina being the root of the tree). Then we algorithm looks at first level managers, the direct parents of leaves. Then at second level managers. But Yasmina was both a first level manager (of the Nonmanager Tarek) and a third level manager (of the Nonmanagers Pierre, Tarek and Sarah). That's why she's appears twice in the final result:once for the "tree branch" which ends @ leaf Tarek, once for the tree B Ranch which ends at leaves Pierre, Tarek and Sarah. The first tree branch contributes 1 direct/indirect report. The second tree branch contributes 4. The right number, which we want, are the sum of the two:5. Thus we just need to change the final query, in the call:

Call With_emulator ("employees_extended", "  Select ID, NAME, manager_id, 0 as REPORTS from  EMPLOYEES  WHERE ID Not in (select manager_id from EMPLOYEES WHERE manager_id are not NULL) ","  select M.id, M.name, m.manager_id, SUM (1+E.R Eports) as REPORTS from  EMPLOYEES M joins employees_extended E on m.id=e.manager_id  GROUP by M.id, M.name, M.manage r_id ","  select ID, name, manager_id, SUM (REPORTS) from the  employees_extended  GROUP by ID, name, Manager_id ", 0," ");

And here is finally the proper result:

 +------+---------+------------+--------------+| ID | NAME | manager_id |   SUM (REPORTS) |+------+---------+------------+--------------+| 29 |        Pedro |            198 |   2 | | 72 |         Pierre |            29 |  0 | | 198 |        John |            333 |  3 | | 333 |       Yasmina |            NULL |  5 | | 692 |        Tarek |            333 | 0 | | 4610 |         Sarah |            29 | 0 |+------+---------+------------+--------------+6 rows in set  

Let's finish by showing the body of the stored procedure. You'll notice that it does heavy use of the dynamic SQL, thanks to prepared statements. Its body does not depend on the particular problem to solve, it's reusable as-is for other with RECURSIVE use cases. I have added comments inside the body and so it should be self-explanatory. If It's not, the feel free to drop a comment the This post, and I'll explain further. Note that it uses temporary tables internally, and the first thing it does was dropping any temporary tables with the same Names.

# usage:the Standard syntax:# with RECURSIVE recursive_table as# (initial_select# UNION all# Recursive_selec T) # final_select;# should be translated by the call With_emulator (Recursive_table, Initial_select, Recursive_select , # final_select, 0, ""). # algorithm:# 1) We have a initial table T0 (actual name is an argument# "Recu Rsive_table "), we fill it with result of initial_select.# 2) We have a union table U, initially empty.# 3) loop:# add ro       WS of T0 to u,# run recursive_select based on T0 and put result into table t1,# if T1 are empty# then leave loop,# Else swap T0 and T1 (renaming) and empty t1# 4) Drop T0, t1# 5) Rename U to t0# 6) Run final Select, send Relult to client# this was for *one* recursive table.# It would being possible to write a SP creating multiple recursive Tables.delimite R | CREATE PROCEDURE with_emulator (recursive_table varchar), # Name of recursive tableinitial_select varchar (65530), # SE Ed a.k.a. AnchorrecursIve_select varchar (65530), # recursive memberfinal_select varchar (65530), # Final SELECT on UNION resultmax_recursion int Unsigned, # Safety against Infinite Loop, use 0 for defaultcreate_table_options varchar (65530) # can add create-table- Time options# to your recursive_table-to-speed up initial/recursive/final selects;  example:# "(KEY (Some_column)) engine=memory") BEGIN declare new_rows int unsigned; DECLARE show_progress int default 0;  # set to 1 to trace/debug execution declare recursive_table_next varchar (120);  DECLARE recursive_table_union varchar (120);  DECLARE recursive_table_tmp varchar (120);  Set recursive_table_next = Concat (recursive_table, "_next");  Set recursive_table_union = Concat (recursive_table, "_union"); Set recursive_table_tmp = Concat (recursive_table, "_tmp"); 

  # Cleanup any previous failed runs SET @str = CONCAT ("DROP temporary TABLE IF E  Xists ", Recursive_table,", ", Recursive_table_next,", ", Recursive_table_union,", ", recursive_table_tmp);  PREPARE stmt from @str; EXECUTE stmt; 

 # If You need to reference recursive_table more than # Once in Recursive_select, remove the temporary word. SET @str = # Create and fill T0 CONCAT ("Create temporary TABLE", recursive_table, "", Create_table_options, "as"  , Initial_select);  PREPARE stmt from @str;  EXECUTE stmt;  SET @str = # Create U CONCAT ("Create temporary TABLE", recursive_table_union, "like", recursive_table);  PREPARE stmt from @str;  EXECUTE stmt;  SET @str = # Create T1 CONCAT ("Create temporary TABLE", Recursive_table_next, "like", recursive_table);  PREPARE stmt from @str;  EXECUTE stmt; If max_recursion = 0 Then Set max_recursion = 100;  # A default to protect the innocent end if; Recursion:repeat # Add T0 to U (this is always UNION all) SET @str = CONCAT ("INSERT into", Recursive_table_un    Ion, "SELECT * from", recursive_table);    PREPARE stmt from @str;    EXECUTE stmt;    # We are done if max depth reached set max_recursion = Max_recursion-1; If not MAX_recursion then if show_progress then select Concat ("Max recursion exceeded");      End If;    Leave recursion;    End If; # fill T1 by applying the recursive SELECT on T0 SET @str = CONCAT ("INSERT into", Recursive_table_next, "", RECU    Rsive_select);    PREPARE stmt from @str;    EXECUTE stmt;    # We are doing if no rows in T1 Select Row_count () to New_rows;    If Show_progress then select Concat (new_rows, "new rows Found");    End If;    If not new_rows then leave recursion;    End If; # Prepare Next Iteration: # T1 becomes T0, to being the source of next run of Recursive_select, # T0 is recycled to be    T1.    SET @str = CONCAT ("ALTER TABLE", recursive_table, "RENAME", recursive_table_tmp);    PREPARE stmt from @str;    EXECUTE stmt; # We use the ALTER TABLE RENAME because RENAME table does not the support temp tables SET @str = CONCAT ("ALTER table", re    Cursive_table_next, "RENAME", recursive_table); PREPARE stmt from @stR    EXECUTE stmt;    SET @str = CONCAT ("ALTER TABLE", Recursive_table_tmp, "RENAME", Recursive_table_next);    PREPARE stmt from @str;    EXECUTE stmt;    # empty T1 SET @str = CONCAT ("TRUNCATE TABLE", recursive_table_next);    PREPARE stmt from @str;  EXECUTE stmt;  Until 0 end repeat;  # Eliminate T0 and T1 SET @str = CONCAT ("DROP temporary TABLE", Recursive_table_next, ",", recursive_table);  PREPARE stmt from @str;  EXECUTE stmt;  # Final (Output) SELECT uses recursive_table name SET @str = CONCAT ("ALTER table", Recursive_table_union, "RENAME",  recursive_table);  PREPARE stmt from @str;  EXECUTE stmt;  # Run Final SELECT on UNION SET @str = final_select;  PREPARE stmt from @str;  EXECUTE stmt;  # No temporary tables may survive:set @str = CONCAT ("DROP temporary TABLE", recursive_table);  PREPARE stmt from @str;  EXECUTE stmt; # We are done:-) end|delimiter;

In the SQL standard, with RECURSIVE allows some nice additional tweaks (Depth-first or breadth-first ordering, cycle Detec tion). In the future posts I'll show how to emulate them too.

With RECURSIVE and MySQL