Name one or several sub-queries to be used within the main query.
This clause makes it easy to simplify large or complex statements which involve sub-queries, particularly when such sub-queries are used several times.
Syntax
Where:
subQueryName
is the alias for the sub-querysubQuery
is a SQL query (e.gSELECT * FROM table
)mainQuery
is the main SQL query which involves thesubQuery
using its alias.
Examples
WITH first_10_users AS (SELECT * FROM users limit 10)
SELECT user_name FROM first_10_users;
WITH first_10_users AS (SELECT * FROM users limit 10),
first_5_users AS (SELECT * FROM first_10_users limit 5)
SELECT user_name FROM first_5_users;
WITH avg_distance AS (select avg(trip_distance) average FROM trips)
SELECT pickup_datetime, trips.trip_distance > avg_distance.average longer_than_average
FROM trips CROSS JOIN avg_distance;