A Common Table Expression (CTE) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT or INSERT statement.

After a CTE is defined, it can be referenced like a table or view can in a SELECT or INSERT statement. A CTE can also be used in a CREATE TABLE...AS SELECT or CREATE VIEW statement as part of its defining SELECT statement. A CTE cannot be used in other SQL statements, such as UPDATE or DELETE, in place of table references.

A CTE is similar to a derived table in that it is not stored and lasts only for the duration of the query. Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query.

Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler. You can divide the query into separate building blocks, which you can then use to build more complex CTEs until the final result set is returned.

Among other things, a CTE may be useful when you need to join the results of a GROUP BY with another table.