Common table expressions
Go Up to SELECT
A Common Table Expression (CTE) returns a temporary result set than can be referenced in SELECT, INSERT, UPDATE, or 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.