12

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

16

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.

  • To avoid repetition (duplication of code), Wouldn't it be better to use variables and temporary tables? – Pacerier Jan 25 '15 at 18:02
  • I wouldn't worry about duplication of code but certainly I would consider and try a version with temporary tables, for performance reasons. – ypercubeᵀᴹ Jan 25 '15 at 18:04
  • 1
    Why do you say that you wouldn't worry about duplication of code? This is outright messy and un DRY. – Pacerier Jan 30 '15 at 19:44
  • 1
    @Pacerier DRY isn't always relevant with DB code. – JNK Jan 30 '15 at 19:55
  • 1
    @Pacerier I would not be surprised if it were not. DB engines need to make educated guesses about what will work best while still guaranteeing to return correct results. Temp tables in general are fine, but DRY leads to terrible performance in DBs in other respects, like user defined functions. – JNK Jan 30 '15 at 20:36
2

That will work but it's a pity it won't provide the advantage of using the WITH clause, that is not to execute the same query several times (with complex queries could be reaaaally slow and very demanding for the database engine; I suffered it).

I would suggest inserting each SELECT defined in the original WITH clause into its own temporary table, and use them inside the query. In MySQL, the temporary table will drop itself once the user session ends.

EDIT:

I just saw this answer in a similar thread which clearly exposes the 3 workarounds with MySQL:

  • TEMPORARY tables
  • DERIVED tables
  • inline views (effectively what the WITH clause represents - they are interchangeable)

https://stackoverflow.com/a/1382618/2906290

and an example of MySQL procedure which creates and drops the temporary tables in case you continue with your session and want to free those resources (I would use it just as an example of the syntax): https://stackoverflow.com/a/5553145/2906290

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.