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 CODE

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 CODE

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

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 CODE

OUTPUT

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

SQL CODE

OUTPUT

Thank You for Visiting Our Blog