Common table expressions creates a virtual table or a result set which exists (in memory) only during the time of execution of the script.
Most commonly asked questions about CTE are
· Why can’t we use temporary table instead of CTE? Yes we can use a temporary table but temporary table will still reside on temporary database (tempdb) and until unless that temporary database gets filled up you should be fine.
· Why can’t we use sub query instead of CTE? Yes we can sub query but if we need to use the same sub query multiple times then CTE is the way to go, as each sub query try’s to access the source database as many times as the number of sub query but CTE accesses it only once resulting in query performance.
· Can CTE be accessed in multiple separate select statements? No if the select statements are not joined then the second select statement will not be able to see the CTE
The syntax is same for CTE in both Oracle and SQL Server except that in SQL Server, we need to have semicolon (;) in front of WITH statement.
Syntax:
--for oracle remove semicolon (;) in front of “with” statement
; WITH CTE_NAME (column name [.., n])
AS
(
--your select statement here
)
Select <column list>
From CTE_NAME;
I commonly use CTE in this blog to create a temporary table with records instead of creating a temporary table and writing bunch of insert statements
SQL Server Example:
; WITH CTE_NAME
AS
(
SELECT CAST(1 AS INT) AS COL1,CAST('SAM' AS VARCHAR(10)) AS COL2_NAME
UNION ALL
SELECT CAST(2 AS INT) AS COL1,CAST('PAT' AS VARCHAR(10)) AS COL2_NAME
UNION ALL
SELECT CAST(3 AS INT) AS COL1,CAST('JON' AS VARCHAR(10)) AS COL2_NAME
)
SELECT * FROM CTE_NAME
Oracle Example:
WITH CTE_NAME
AS
(
SELECT CAST(1 AS NUMBER) AS COL1,CAST('SAM' AS VARCHAR(10)) AS COL2_NAME FROM DUAL
UNION ALL
SELECT CAST(2 AS NUMBER) AS COL1,CAST('PAT' AS VARCHAR(10)) AS COL2_NAME FROM DUAL
UNION ALL
SELECT CAST(3 AS NUMBER) AS COL1,CAST('JON' AS VARCHAR(10)) AS COL2_NAME FROM DUAL
)
SELECT * FROM CTE_NAME;
The above can be achieved using a temp table, for more info click here
Thank you for reading this article,
check out my other SQL Server vs. Oracle posts.