In our 3rd SQL session we covered Common Table Expression (CTEs). We can think of CTEs as temporary tables created for use in a SQL query. We use CTEs to breakdown longer queries into a structure that is easy to logically follow and debug. By making CTEs at the start of the query we can ensure that the component parts we use later in the main query are being created correctly before then drawing upon them in the main query.
CTEs are defined with the following syntax:
WITH cte_name (column1, column2, ...) AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT ...
FROM cte_name;
Naming the columns for the CTE is optional.
Now to walk-through a simple example of a CTE.
I am working with F1 data in this example and am going to keep it very simple:
I want to return a table with each racers fasted speed clocked at Monaco in 2019 and 2021:
Before writing the query I want to know my data; I know that fasted speeds are recorded in the results table, if I want driver information like name I ill have to bring in that information from the drivers table. But if I want two speeds in the same row I cannot simply filter on both Years in the WHERE part of my query as I will have a row for each year rather than both speeds in one row. Finally to get the specific races (Monaco in 2019 and 2021) I am going to need the races table.
I have thus highlighted in green the fields that I need to know to complete the query and those in red as the ones I ultimately want to return.
I can thus use a CTE to make a temporary table with the 2019 fastest speeds and then select the speed column in my main query (assuming I join on the temporary table) along with the 2021 fastest speeds:
