MySQL equivalent of WITH in oracle

MySQLmysql-5.5oracle

Is there a MySQL equivalent of the WITH clause in Oracle?

Best Answer

There is not. Unless (until) one develops it (MySQL is open-source, anyone can contribute.)

The ANSI/ISO SQL WITH keyword is used to define Common Table Expressions (CTEs) and it simplifies complex queries with one or several nested references. It's available in Oracle, Postgres, SQL-Server, DB2 but not in MySQL.

The final query may have references (usually in the FROM clause but they could be in any other part) to anyone of the common table expressions, one or more times. The query can be written (without CTEs) in MySQL using derived tables but the references have to be made repeatedly.

Example of a silly query showing all persons born in the 50s and in the month of July and the number of all persons born in the same year:

WITH a AS
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01' 
    ) 
, b AS
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM a
      GROUP BY birthyear 
    ) 
SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM a JOIN b
  ON a.birthyear = b.birthyear 
WHERE MONTH(a.birthdate) = 7 ;

In MySQL, it could be written as:

SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM 
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01' 
    ) AS a 
  JOIN 
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM 
        ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
          FROM persons
          WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01' 
        ) AS aa
      GROUP BY birthyear
    ) AS b
  ON a.birthyear = b.birthyear 
WHERE MONTH(a.birthdate) = 7 ;

Notice the duplication of code for the derived table a. In more complex queries, code would have to be written multiple times.

Related Question