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 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.

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.

Example 2

In this example, we will use 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 yearly income is greater than or equal to 60000. For this SQL 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 count the total number of employees present in each department, their sales amount, and total 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 WHERE Clause to restrict the Employees. It display 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 SQL Server Derived table Query displays Department names whose employees 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