WITH

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-query
  • subQuery is a SQL query (e.g SELECT * FROM table)
  • mainQuery is the main SQL query which involves the subQuery using its alias.

Examples

  2. 1. WITH first_10_users AS (SELECT * FROM users limit 10)
  3. 2. SELECT user_name FROM first_10_users;
  2. 1. WITH first_10_users AS (SELECT * FROM users limit 10),
  3. 2. first_5_users AS (SELECT * FROM first_10_users limit 5)
  4. 3. SELECT user_name FROM first_5_users;
  2. 1. WITH avg_distance AS (select avg(trip_distance) average FROM trips)
  3. 2. SELECT pickup_datetime, trips.trip_distance > avg_distance.average longer_than_average
  4. 3. FROM trips CROSS JOIN avg_distance;