How to Write a Common Table Expression

A common table expression (also called CTE) is the result set of a select which is stored as an intermediate table, during the query execution. You can refer to the CTE name in the query as any other valid table. However after the query ends, it is destroyed, and you can’t refer anymore.

with my_cte as 
  ( 
    select emp_id,
      last_name, 
      salary,
      dep_id
    from employee
  )
select dep_id, last_name, salary
from  my_cte
where dep_id = 1;

In the previous query my_cte is a CTE and can be referenced as a regular table in the outer select.

IN THIS PAGE