CTE is short for Common Table Expression. It is a temporary result set, which is used as a sub query for a main query (SELECT, INSERT, UPDATE or DELETE). CTEs are used for better readability and organization of SQL queries.

Main steps:

  • Assign a name to a specific temporary result set – the CTE
  • Use CTE in your main query, by referring to the CTE just as you do with normal table.



Here’s what’s happening in the above code:

  1. The CTE definition starts with CTE special keyword with
  2. After the with, the first CTE’s name is total_sale, followed with another keyword as
  3. Inside the parentheses, an inner query starting with Select creates a temporary result set for the temporary table total_sale
  4. The second CTE named rep_sale is created following the comma ,
  5. Same as before, CTE rep_sale is followed with the keyword as, and parentheses, in which there is inner query to define another temporary result set for it.
  6. Now that we have our CTEs, we can use them in our main query referencing the CTEs, just as how we normally do with other tables.
  7. Note: CTE only lives within the scope of the statement immediately following the with clause as it is temporary result set. They cannot be used anywhere else. Using CTEs is more organized than creating temporary table and dropping them from the data base after usage.

I have found CTE very useful for me when creating longer queries. They are much more readable and easier to follow. Please give it a try and you will find your SQL scripts much more organized.

Thanks for reading. See you next time.



Thao Phuong Tran
Author: Thao Phuong Tran

Thao is an experienced 3PL Warehouse Administrator and Retail Assistant Manager. Driven by a passion for data analytics and information technology, she takes pride in providing the best analysis to help businesses in making data driven decisions and tackling their issues. As a Data Analyst Consultant at The Data School, her goals include learning from the best people in the data industry and up skilling herself with relevant and powerful tools like Tableau, Alteryx, Power BI, etc. She adores applying process automation to improve her productivity and efficiency at work. When Thao is not keeping busy with data, you can find her enjoying collecting Pandora charms and baking chocolate mint cookies. She also loves dining out and a meal that never fails her is spicy Pad Thai paired with a Vodka Sunrise!