SQL Derived Table

The SQL Server Derived Table is nothing but a Subquery used in the From Clause. For this example, we will use two tables (Employee Details and Department) in our Database. Data present in the Employee Details Table is:

Data present in the Department Table is:

SQL Derived Table Example

It is a simple example to demonstrate the derived table. The following SQL Query will display all the columns present in the Employees table whose sales amount is greater than 500.

SELECT * FROM
(
	SELECT [EmpID]
		,[FirstName]
		,[LastName]
		,[Education]
		,[YearlyIncome]
		,[Sales]
		,[DeptID]
	FROM [EmployeeDetails]
) AS [Derived Employee Details]
WHERE [Sales] > 500

The first SELECT * statement derives columns from the inner select statement or subquery.

Example 2

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

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

SQL Server Derived Table Example 3

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

For this 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 and Joins articles.

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]

In this SQL Derived Table Example, we are just adding the WHERE Clause to restrict the Employees. It displays Department names whose employees count is greater than 1.

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 employee’s Total Income is greater than 100000.

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