Difference between CTE, Temp Tables, Derived tables

In SQL Server, when working with large data sets (or massive records), we might need to store the intermediate results in a temporary query. So, we can access them further. SQL Server provides multiple options to achieve the same: CTE, Temporary Tables, Derived Tables, and Table Variables. Let us see the Difference between CTE, Temp Tables, Derived Tables, and Table variables in SQL Server with practical examples.

For this SQL difference between CTE, Temp Tables, Derived tables demo, we use two tables (Employee Details and Department) from our Database. Data available in the Employee Details Table is:

Data already in the Department Table is:

Difference between CTE, Temp Tables, Derived tables and Table Variable

In this section, our objective is to display the total employees available in each department. Along with the sales amount, and their total annual salaries. The basic structure of the SQL query that we are going to use in all the examples contains

• We are going to use an SQL INNER JOIN to pick a column from both the Employees and the Department tables.
• And we use SQL GROUP BY to aggregate the columns. I suggest you visit the SQL Aggregate Functions article.
• Last we use CTE, Table variable, Temporary Tables, Derived tables to filter the data.

SQL Derived Table Example

We are going to achieve our objective using the Derived Column. I suggest you refer to the SQL Derived Table article on the SQL tutorial page to understand the query.

-- Difference between CTE, Temp Tables, Derived tables , and Table variable 
SELECT * FROM
(
	SELECT  [DepartmentName]
		,COUNT(EmpID) AS [Total Employees in this Department]
		,SUM([YearlyIncome]) AS [Total Income]
		,SUM([Sales]) AS [Total Sale]
	FROM [EmployeeDetails]
	INNER JOIN [Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]
) AS [Derived Employee Details]
WHERE [Total Income] > 100000

SQL Temporary Table Example

We are going to achieve our objective using a temporary table. First, we are using the SQL SELECT INTO INSERT statement to insert records into the Local Temporary Table. Next, we select all records from that temporary table whose Total Income is greater than 100000. I suggest you refer to the SQL Temp Table article to understand the query.

TIP: Please refer to the SQL SUM and SQL COUNT functions.

--Inserting records into temp table #TempEmployeeDetails
SELECT  [DepartmentName]
	,COUNT(EmpID) AS [Total Employees in this Department]
	,SUM([YearlyIncome]) AS [Total Income]
	,SUM([Sales]) AS [Total Sale]
INTO #TempEmployeeDetails
FROM [EmployeeDetails]
	INNER JOIN [Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
GROUP BY [DepartmentName]

-- Selecting Columns from Temp Table
SELECT [DepartmentName]
	  ,[Total Employees in this Department]
	  ,[Total Income]
	  ,[Total Sale]
FROM #TempEmployeeDetails
WHERE [Total Income] > 100000

SQL Table Variable Example

This time, we are going to use a Table variable to achieve our objective. First, we declared a Table Variable. Next, we are using the SQL INSERT INTO SELECT statement to insert records into that Table variable. Lastly, we are selecting all the records from the Table Variable whose Total Income is greater than 100000. I suggest you refer to the SQL Table Variable to understand the query.

-- Creating Table Variable
DECLARE @EmployeeTableVariable TABLE
(
	[DepartmentName] VARCHAR(255) NULL,
	[Total Employees in this Department] INT NULL,
	[Yearly Income] FLOAT NULL,
	[Sales Amount] FLOAT NULL
)

-- Inserting Data into Table Variable
INSERT INTO @EmployeeTableVariable
    SELECT  [DepartmentName]
	   ,COUNT(EmpID) AS [Total Employees in this Department]
	   ,SUM([YearlyIncome]) AS [Yearly Income]
	   ,SUM([Sales]) AS [Sales Amount]
    FROM [SQLTEST].[dbo].[EmployeeDetails]
	INNER JOIN [SQLTEST].[dbo].[Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
    GROUP BY [DepartmentName]

-- Selecting records from the Table variable
SELECT [DepartmentName], [Total Employees in this Department], [Yearly Income], [Sales Amount]
FROM @EmployeeTableVariable
WHERE [Yearly Income] > 100000

SQL Common Table Expression Example

This time we are going to use Common table expression (or CTE) to achieve our object. First, we create a CTE. Next, we are selecting all the records from that CTE whose Total Income is greater than 100000. I suggest you refer to the SQL Server CTE to understand the query.

-- Creating CTE (Common Table Expression)
WITH EmployeeExpression ( [DepartmentName], 
			  [Total Employees in this Department], 
			  [Yearly Income], 
			  [Sales Amount]
			) 
AS
(
    SELECT  [DepartmentName]
	   ,COUNT(EmpID) AS [Total Employees in this Department]
	   ,SUM([YearlyIncome]) AS [Yearly Income]
	   ,SUM([Sales]) AS [Sales Amount]
	FROM [SQLTEST].[dbo].[EmployeeDetails]
	INNER JOIN [SQLTEST].[dbo].[Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]
)

-- Selecting records from the EmployeeExpression CTE
SELECT [DepartmentName], 
       [Total Employees in this Department], 
       [Yearly Income], 
       [Sales Amount]
FROM EmployeeExpression
WHERE [Yearly Income] > 100000

SQL View Example

You can also use Views to achieve our object. First, we create a view that is selecting all the matching records from both the tables along with aggregations. Next, we are selecting all the records from View whose Total Income is greater than 100000. Please refer Views in SQL to know the query.

--Creating a View
CREATE VIEW vNumberofEmployees
AS
	SELECT  [DepartmentName]
		,COUNT(EmpID) AS [Total Employees in this Department]
		,SUM([YearlyIncome]) AS [Total Income]
		,SUM([Sales]) AS [Total Sale]
	FROM [EmployeeDetails]
	INNER JOIN [Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	GROUP BY [DepartmentName]

-- Selecting Records from vNumberofEmployees View
SELECT * FROM vNumberofEmployees
WHERE [Total Income] > 100000