SQL Function Reference

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;