In SQL, a Common Table Expression (CTE) is a useful tool that lets you combine multiple SQL queries.
More specifically, you can create a SELECT query and use its values in a second query. It's similar to creating a temporary view that you can use in a query.
CTEs are extremely versatile, and are used in:
- Working with data with different granularity (e.g. find the number of sales per city, state and country)
- Breaking down a complex query into multiple steps
- Recursive queries
and more!
Syntax
Use the WITH keyword to name a query, then use its values and columns in another query.
To use values and columns from your first query, make use of FROM or JOIN
Usage Example
Write a query:
Name and store that query (making sure to remove the ';' at the end):
Then use your first query inside another query.
In this example, we've used a CTE to add an aggregated scalar number (a single value for the MIN()/fastest ever recorded lap time) to a table.