Difference between CTE, Temp Tables, Derived tables

In SQL Server, while working with the large data sets (or massive records), we might require to store the intermediate results in the 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 Inner Join to pick a column from both Employees and Department tables.
• And, we use GROUP BY to aggregate the columns. I suggest you visit the Aggregate Functions article.
• Last we use CTE, Table variable, Temporary Tables, Derived tables to filter the data.

Derived Table Example

We are going to achieve our object using the Derived Column. I suggest you refer Derived Table article 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 object using the Temporary table. First we are using the Insert Into Statement to insert records into Local Temporary Table. Next, we are selecting all the records from that temporary table whose Total Income is greater than 100000. I suggest you to refer Temp Table article to understand the query.

--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 Table variable to achieve our object. First, we declared a Table Variable. Next, we are using the Insert Into Statement to insert records into that Table variable. Lastly, we are selecting all the records from Table Variable, whose Total Income is greater than 100000. I suggest you refer to the 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 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 View 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