Could anyone explain to me what a CTE in SQL is, in a clear and concise manner?

CodePudding user response：

CTE is Common Table Expression. Think of this as a single query and it will give you a result set. Now you can use that in a subsequent query without having to have too complex a bunch of garbage all nested. For example

with MyAliasJustManagers as
( select 
         E.EmployeeID,
         E.FirstName as ManagerFirstName,
         E.LastName as ManagerLastName
      from
         Employees E
      where
         E.IsManager = 1
)
-- NOW, you can query with the above sample
select
      E.FirstName,
      E.LastName,
      M.ManagerFirstName,
      M.ManagerLastName
   from
      Employees E
         JOIN MyAliasJustManagers M
            on E.ManagerID = M.EmployeeID

You can see the simplicity above. You can predefine multiple With CTE to be used in the final SQL query, otherwise the above would have looked something like

select
      E.FirstName,
      E.LastName,
      M.ManagerFirstName,
      M.ManagerLastName
   from
      Employees E
         JOIN ( select 
                      E.EmployeeID,
                      E.FirstName as ManagerFirstName,
                      E.LastName as ManagerLastName
                   from
                      Employees E
                   where
                      E.IsManager = 1
             ) M
            on E.ManagerID = M.EmployeeID

Notice the second has the query embedded within the outer. If your queries are complex and have many other criteria, it is probably easier to have that stand-alone query as a WITH simplified. This way you know that one component works without causing confusion being embedded with multiple and figuring out what is causing problems outright.

CodePudding user response：

A CTE (Common Table Expression) is a type of subquery that can be included in a bigger SQL query.

It typically expresses intermediate results that can be further processed by another CTE or by the main query.

The simplest use of a CTE can look like:

with a as (select ...) -- we define a CTE named "a"
select * from a;       -- we use the CTE

A query can define multiple CTEs, and each one can use a previously defined CTE. For example:

with a as (select ...),
     b as (select ...),
     c as (select * from a join b on ...)
select * from c;

Finally, even though CTEs conceptually represent intermediate results, the database is not forced to materialize this result, but can do any kind of dirty tricks to compute the final result of the query. In particular, it can internally rewrite the query or it can internally combine multiple CTEs into one without you noticing.