Common table expressions
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
ALTERNATE VERSION You can define a CTE within a statement using the
WITHoperator followed by a
(<column name>), you can define more than one column, followed by an
ASoperator and a
(<query>), you can define more than one CTE after the
WITHoperator. Once you define one or more CTEs you can use a query that references the defined CTE columns.
This is a sample that will be replaced, PLACE HOLDER ONLY!!
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????
Recursive CTE's and Non-recursive CTE's
A non-recursive CTE Recursive CTE's can reference themselves and run until they reach an end condition.
Examples/USAGE more info needed
- Use of Recursive and Non recursive CTE's
- join CTEs?
- join CTE to normal table?
- Self Join?
- sample codes
Derived tables vs CTE/Views vs CTE?