MySQL equivalent of WITH in oracle

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.

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