SQL Derived Table

The SQL Derived Table is nothing but a Subquery used in the From Clause. In this article, we will show you, How to write Derived Table in SQL Server with practical examples. For this, We are going to use two tables (Employee Details and Department) present in our [SQL Test] Database.

Data present in the Employee Details Table is:

Data present in the Department Table is:

SQL Derived Table Example 1

This is the simple example to demonstrate the derived tables in SQL Server. Following SQL Query will display all the columns present in Employees table whose sales amount is greater than 500.

--SQL Server Derived Table Example
USE [SQLTEST]
GO
SELECT * FROM
(
	SELECT [EmpID]
		,[FirstName]
		,[LastName]
		,[Education]
		,[YearlyIncome]
		,[Sales]
		,[DeptID]
	FROM [EmployeeDetails]
) AS [Derived Employee Details]
WHERE [Sales] > 500

First SELECT * statement is deriving columns from the inner select statement.

OUTPUT

SQL Derived Table Example 2

In this example, we will use Joins concept inside the Derived Tables. The following SQL Query will display all the matching columns present in Employees and Department tables whose sales amount is greater than 500, and yearly income is greater than or equal to 60000. For this, we are using the INNER JOIN.

--SQL Server Derived Table Example
USE [SQLTEST]
GO
SELECT * FROM
	(
		SELECT [EmpID]
			,[FirstName]
			,[LastName]
			,[Education]
			,[YearlyIncome]
			,[Sales]
			,[DepartmentName]
		FROM [EmployeeDetails]
		INNER JOIN [Department]
			ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	) AS [Derived Employee Details]
WHERE [Sales] > 500 AND [YearlyIncome] >= 60000

OUTPUT

SQL Derived Table Example 3

The following SQL Query will display count the total number of employees present in each department, their sales amount, and their total annual salaries. For this, we are using Inner Join to get a column from both Employees and Department tables. Next, we used GROUP BY Clause to aggregate the columns. Please refer Aggregate Functions article to understand these functions.

--SQL Server Derived Table Example
USE [SQLTEST]
GO
SELECT * FROM
(
	SELECT  [DepartmentName]
		,COUNT(DepartmentName) 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]

OUTPUT

Here we are just adding WHERE Clause to restrict the Employees. The following SQL Query will display Department names whose employees count is greater than 1.

--SQL Server Derived Table Example
USE [SQLTEST]
GO
SELECT * FROM
	(
		SELECT  [DepartmentName]
		  ,COUNT(DepartmentName) 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 Employees in this Department] > 1

OUTPUT

The following SQL Query will display Department names whose employees Total Income is greater than 100000.

--SQL Server Derived Table Example
USE [SQLTEST]
GO
SELECT * FROM
	(
		SELECT  [DepartmentName]
		  ,COUNT(DepartmentName) 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

OUTPUT