WHAT IS CTE?

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.

Syntax:

Example:

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

 

The Data School
Author: The Data School