WITH
A WITH expression defines a temporary dataset that is available to be referenced in subsequent queries
MySQL
WITH name_cte [(column_1 [, column_2 ])] AS (subquery)
name_cte
The name of the table expression by which it will be available
subquery
Subquery, the result of which can be used in other parts of the SQL query
Examples
MySQL
WITH Aeroflot_trips AS
                (SELECT TRIP.* FROM Company
                    INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")
            
            SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;