SQL Derived Table

The SQL Derived Table is nothing but a Subquery used in the From Clause. For this SQL Server derived table example, 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

It is a simple example to demonstrate the SQL Server derived table. The following SQL Query will display all the columns present in the 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 or subquery.

Derived Table Example 2

In this example, we will use Joins concept inside the Derived Tables. The following 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 demonstration, 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

Derived Table Example 3

The following Query count the total number of employees present in each department, their sales amount, and total annual salaries.

For this SQL server Derived Table example, 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 to the Aggregate 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]

Here we are just adding WHERE Clause to restrict the Employees. It 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

The following Derived table Query displays 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