Common table expressions

From InterBase
Jump to: navigation, search

Introduction

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

ALTERNATE VERSION You can define a CTE within a statement using the WITH operator followed by a <cte name> and (<column name>), you can define more than one column, followed by an AS operator and a (<query>), you can define more than one CTE after the WITH operator. 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?