COMMON TABLE EXPRESSION (CTE)
- Unlike NESTED TABLE EXPRESSION CTE is not specified in FROM clause of the query.
- But CTE is specified before the full-select in a SELECT statement using WITH keyword.
- A single query can contain multiple CTEs but keyword WITH is used only once.
- Inside query CTE table is treated as any other normal table.
- By default CTE table use column names from query it is drived from(Or system generated name in case of some SQL Funtion like SUM() is used) , But there is option to rename CTE table columns
WITH AVSAL AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYE GROUP BY WORKDEPT) SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL FROM AVSAL GROUP BY WORKDEPT;
In above code “WITH AVSAL AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYE GROUP BY WORKDEPT)” defines a CTE with name AVSAL means now there is a temporary table with name AVSAL available to this query. In next line “SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL FROM AVSAL GROUP BY DEPT” select statement is used to get data from CTE table AVSAL like any other table.
Rename columns for CTE
WITH AVSAL(DEPARTMENT,AVGSALARY) AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYE GROUP BY WORKDEPT) SELECT DEPARTMENT, MAX(AVGSALARY) AS MAXAVGSAL FROM AVSAL GROUP BY DEPARTMENT;
In above query “WITH AVSAL(DEPARTMENT,AVGSALARY) AS” renames CTE table AVSAL’s columns to DEPARTMENT and AVGSALARY.
Using Multiple CTEs in single Query
This is a sample of complex query where Multiple CTE tables are being used in combination with SQL JOINS. For multiple CTEs in single query there is only single WITH clause.
WITH Ctable1 AS (SELECT C1Field1, row_number() over(partition by C1Field2) as rowNbr FROM SampleTable1 WHERE C1Field5 = "XYZ" ), Ctable2 AS (SELECT SampleTable1.* , row_number() over(order by C2Field3 desc) as rowNbr FROM SampleTable2 INNER JOIN SampleTable3 ON C2Field = SampleTable3.C3Field WHERE C2Field5 <= "ABC") SELECT * FROM Ctable1 t1 FULL JOIN Ctable2 t2 ON t1.C1Field1 = t2.C2Field1 WHERE t1.rowNbr = 1 OR t2.rowNbr = 1
In Above two temporary tables Ctable1 and Ctable2. Ctable1 is created using simple select statement with OLAP function row_number. Ctable2 is created by joining two tables SampleTable2 and SampleTable3.
And finally "SELECT statement" which join Ctable1 and Ctable2 using FULL JOIN.