How do you use the WITH clause in MySQL

Question

I am converting all my SQL Server queries to MySQL and my queries that have WITH in them are all failing  Here s an example   WITH t1 AS        SELECT article    userinfo    category        FROM question      INNER JOIN userinfo ON userinfo user userid   article article ownerid      INNER JOIN category ON article article categoryid   category catid      WHERE article article isdeleted   0   SELECT t1   FROM t1 ORDER BY t1 article date DESC LIMIT 1  3

User · Answer

Common Table Expression  feature is not available in MySQL  so you have to go to make a view or temporary table to solve  here I have used a temporary table   The stored procedure mentioned here will solve your need  If I want to get all my team members and their associated members  this stored procedure will help   ---------------------------------- user id       team id ---------------------------------- admin         NULL ramu          admin suresh        admin kumar         ramu mahesh        ramu randiv        suresh -----------------------------------   Code   DROP PROCEDURE  user hier    CREATE DEFINER  root   localhost  PROCEDURE  user hier  in team id varchar 50   BEGIN declare count int  declare tmp team id varchar 50   CREATE TEMPORARY TABLE res hier user id varchar 50  team id varchar 50  engine memory  CREATE TEMPORARY TABLE tmp hier user id varchar 50  team id varchar 50  engine memory  set tmp team id   team id  SELECT COUNT    INTO count FROM user table WHERE user table team id tmp team id  WHILE count gt 0 DO insert into res hier select user table user id user table team id from user table where user table team id tmp team id  insert into tmp hier select user table user id user table team id from user table where user table team id tmp team id  select user id into tmp team id from tmp hier limit 0 1  select count    into count from tmp hier  delete from tmp hier where user id tmp team id  end while  select   from res hier  drop temporary table if exists res hier  drop temporary table if exists tmp hier  end   This can be called using   mysql gt call user hier   admin

User · Answer

In Sql the with statement specifies a temporary named result set  known as a common table expression  CTE   It can be used for recursive queries  but in this case  it specifies as subset  If mysql allows for subselectes i would try   select t1    from                SELECT  article                         userinfo                         category                FROM    question INNER JOIN                      userinfo ON userinfo user userid article article ownerid INNER JOIN category ON article article categoryid category catid             WHERE   article article isdeleted   0        t1 ORDER BY t1 article date DESC Limit 1  3

User · Answer

I followed the link shared by lisachenko and found another link to this blog  http   guilhembichot blogspot co uk 2013 11 with-recursive-and-mysql html  The post lays out ways of emulating the 2 uses of SQL WITH   Really good explanation on how these work to do a similar query as SQL WITH   1  Use WITH so you don t have to perform the same sub query multiple times  CREATE VIEW D AS  SELECT YEAR  SUM SALES  AS S FROM T1 GROUP BY YEAR   SELECT D1 YEAR   CASE WHEN D1 S gt D2 S THEN  INCREASE  ELSE  DECREASE  END  AS TREND FROM  D AS D1   D AS D2 WHERE D1 YEAR   D2 YEAR-1  DROP VIEW D    2  Recursive queries can be done with a stored procedure that makes the call similar to a recursive with query   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 IS NOT NULL         SELECT M ID  M NAME  M MANAGER ID  SUM 1 E REPORTS  AS REPORTS   FROM EMPLOYEES M JOIN EMPLOYEES EXTENDED E ON M ID E MANAGER ID   GROUP BY M ID  M NAME  M MANAGER ID     SELECT   FROM EMPLOYEES EXTENDED   0          And this is the code or the stored procedure    Usage  the standard syntax      WITH RECURSIVE recursive table AS       initial SELECT       UNION ALL       recursive SELECT      final SELECT    should be translated by you to    CALL WITH EMULATOR recursive table  initial SELECT  recursive SELECT                       final SELECT  0          ALGORITHM    1  we have an initial table T0  actual name is an argument    recursive table    we fill it with result of initial SELECT    2  We have a union table U  initially empty    3  Loop      add rows of T0 to U      run recursive SELECT based on T0 and put result into table T1      if T1 is 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 is for  one  recursive table    It would be possible to write a SP creating multiple recursive tables   delimiter    CREATE PROCEDURE WITH EMULATOR  recursive table varchar 100     name of recursive table initial SELECT varchar 65530     seed a k a  anchor recursive SELECT varchar 65530     recursive member final SELECT varchar 65530     final SELECT on UNION result max recursion int unsigned    safety against infinite loop  use 0 for default create table options varchar 65530    you 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 EXISTS    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 union    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       recursive SELECT       PREPARE stmt FROM  str      EXECUTE stmt        we are done if no rows in T1     select row count   into 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 be 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 ALTER TABLE RENAME because RENAME TABLE does not support temp tables     SET  str         CONCAT  ALTER TABLE    recursive 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

User · Answer

Mysql Developers Team announced that version 8 0 will have Common Table Expressions in MySQL  CTEs   So it will be possible to write queries like this    WITH RECURSIVE my cte AS     SELECT 1 AS n   UNION ALL   SELECT 1 n FROM my cte WHERE n lt 10   SELECT   FROM my cte   ------    n       ------       1        2        3        4        5        6        7        8        9       10    ------  10 rows in set  0 00 sec

User · Answer

MySQL prior to version 8 0 doesn t support the WITH clause  CTE in SQL Server parlance  Subquery Factoring in Oracle   so you are left with using    TEMPORARY tables DERIVED tables   inline views  effectively what the WITH clause represents - they are interchangeable    The request for the feature dates back to 2006   As mentioned  you provided a poor example - there s no need to perform a subselect if you aren t altering the output of the columns in any way     SELECT        FROM ARTICLE t     JOIN USERINFO ui ON ui user userid   t article ownerid     JOIN CATEGORY c ON c catid    t article categoryid    WHERE t published ind   0 ORDER BY t article date DESC     LIMIT 1  3   Here s a better example   SELECT t name         t num   FROM TABLE t   JOIN  SELECT c id                COUNT     num            FROM TABLE c          WHERE c column    a        GROUP BY c id  ta ON ta id   t id

User · Answer

That feature is called a common table expression http   msdn microsoft com en-us library ms190766 aspx  You won t be able to do the exact thing in mySQL  the easiest thing would to probably make a view that mirrors that CTE and just select from the view  You can do it with subqueries  but that will perform really poorly  If you run into any CTEs that do recursion  I don t know how you d be able to recreate that without using stored procedures   EDIT  As I said in my comment  that example you posted has no need for a CTE  so you must have simplified it for the question since it can be just written as  SELECT article    userinfo    category   FROM question      INNER JOIN userinfo ON userinfo user userid article article ownerid      INNER JOIN category ON article article categoryid category catid      WHERE article article isdeleted   0  ORDER BY article date DESC Limit 1  3

User · Answer

I liked  Brad s answer from this thread  but wanted a way to save the results for further processing  MySql 8    -- May need to adjust the recursion depth first SET   cte max recursion depth   10000   -- permit deeper recursion  -- Some boundaries  set  startDate    2015-01-01         endDate    2020-12-31      -- Save it to a table for later use drop table if exists tmpDates   create temporary table tmpDates as      -- this has to go  before  the  with   Duh-oh       WITH RECURSIVE t as           select  startDate as dt       UNION         SELECT DATE ADD t dt  INTERVAL 1 DAY  FROM t WHERE DATE ADD t dt  INTERVAL 1 DAY   lt    endDate           select   FROM t     -- need this to get the  with  s results as a  result set   into the  create     -- Exists  select   from tmpDates      Which produces   dt          ----------  2015-01-01  2015-01-02  2015-01-03  2015-01-04  2015-01-05  2015-01-06

[mysql] How do you use the "WITH" clause in MySQL?

Examples related to mysql

Examples related to common-table-expression

Examples related to with-clause

Examples related to subquery-factoring

Examples related to mysql-8.0