Common table expressions
Go Up to SELECT
A Common Table Expression (CTE) returns a temporary result set than can be referenced in
DELETE statements. CTE's can help to simplify complex subqueries and joins, making the code more readable and easier to maintain. CTE's can also be seen as alternatives to views and derived tables. CTEs are part of the ANSI SQL 99 specification.
You define a CTE using the
WITH operator. The CTE compromises two sections. On the first section, define the
<cte name> of the operator followed by the
(<column name>) it contains, you can define more than one column. You can define more than one CTE after the
WITH operator. The next section, known as query definition, uses the
AS operator followed by the
(<SELECT query>) to populate the columns set on the first section. After defining a CTE you can use a
<query> on the defined CTE.
WITH Cities_CTE (Zip, City) AS ( SELECT zip_code, city_name FROM NationalDB.city_info ) SELECT Zip, City FROM Cities_CTE
When defining CTE's it's not required to use the same name table and column names as the referenced ones.