A common table expression (CTE) is a temporary result set that stores intermediate results. They are an incredibly powerful tool that SQL developers can use to solve complex problems and simplify complicated queries.


These queries make it possible for developers to work with data more efficiently and effectively. CTEs are also helpful when performing multiple operations on the same set of data.


Common Table Expression (CTE) store data in a single table using the WITH keyword. The data is stored as columns and rows, and the column name can be different from the orignal name.


A CTE is a simple way of creating a temporary table that can be joined within an SQL query. CTEs are used for more complex queries, need to be effecient with repetition, or need the ability to use JOINs, UNIONs, aggregate functions, or subqueries.


How to write CTE queries?


The first step is to figure out how to write queries:

Let's take example table customer with some data, we will be performing queries step by step:


1. The first step is to start with CTE with "WITH"


With customer_cte as

( 

      SELECT * FROM customer

       WHERE city='Delhi' 

)


2. The CTE statement must be followed by single select, insert, update or delete statement. Here's the final result:


With customer_cte as

( 

      SELECT * FROM customer

       WHERE city='Delhi' 

)

SELECT * FROM customer_cte;