Derived Table

A derived table is an example of a subquery that is used in the FROM clause of a SELECT statement to retrieve a set of record.

when a derived table is defined a database object is not created, and the derived table falls out of scope when the outer query completes.

(No need to-R & I-- Retrieve From One Query & insert into One Table)

--Drop Table Project

GO

CREATE TABLE Project

(  

Emp_Id INT,  

Emp_Name VARCHAR(30),  

Project_Name VARCHAR(30) ,

CreatedDateTime Datetime Default getdate()

) 

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','Survey managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(1,'Sagar','HR managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(2,'Rohan','Wealth managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Wealth managment')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Internal Portal')

Insert Into Project (Emp_Id,Emp_Name,Project_Name) values(3,'Roshan','Wealth managment')

Select * from Project order by 2,3

-----------------------------Derived Table---------------

In this example T act as a Derived Table

--How to Find Duplicate Records?

Select * from 

(

Select Emp_Id,Emp_Name,Project_Name,

ROW_NUMBER()Over(partition by Emp_Id,Emp_Name,Project_Name Order By Emp_ID) As RN 

from Project 

)

T where RN>1

----------------------Join Derived Table

Select * from 

(

Select Id,FirstName,LastName,DepartmentId

From [dbo].[Employee]

) T Inner join [dbo].[Department] D

on T.DepartmentId=D.DepartmentId

We can function in derived table & vice versa.

Create FUNCTION FnConcatName

(

@firstname varchar(20),

@Lastname varchar(20)

)

RETURNS varchar(50)

AS

BEGIN

   Declare @FullName varchar(50)

   

   Set @FullName=@firstname+' '+@Lastname

   RETURN @FullName

END

Select dbo.FnConcatName('Ajit','mehta') As FullName

call function in derived table

Select * from 

(

Select Id,

   FirstName,

   LastName,

   DepartmentId,

   (Select dbo.FnConcatName(FirstName,LastName)) As FullName

From [dbo].[Employee]

) T Inner join [dbo].[Department] D

on T.DepartmentId=D.DepartmentId

group by example as derived table,

Select C.Name,T.PatientCount from City C

Inner join 

(

Select Cityid, Count(1) PatientCount

from Patient P

Group by CityID

) T

On C.CityID=T.CityID

order By 2 DESC

-------------------------------------------------------------------------

Where it Stored?  --RAM--> memory

Can we create Index on it?  --No

Can we create constraint on it? --No

Can we Create Derived Table in Stored Procedure? --Yes

Can we Create Derived Table in Function? --Yes

How to Drop it? --No Need to Drop explicitely

scope --only to same SQL statement

Name overwritten ---yes, in one session we can use same name for derived table.