Join Table Value Function with Table in SQL

This blog will explain one of the most important question in interview?

Is How to Join Table Valued Function with Table?

For Ex

1. Create table Emp as
   

   CREATE TABLE [dbo].[Emp](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [varchar](50) NULL,
   [City] [varchar](50) NULL,
   CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED
   (
   [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

   And Insert Record into Emp table as
   
   
   
   
2. Create Table value function as
   

   GO
   -- =============================================
   -- Author: <Author:- Vishvajeet>
   -- Create date: <Create:- 23/11/2014>
   -- Description: <Description:- Fn_Select Employee Salary>
   -- =============================================
   Create FUNCTION [dbo].[fn_Salary]
   (
   @id Int
   )
   RETURNS TABLE
   AS
   RETURN
   (
   SELECT Salary from Salary where ID=@id
   )

   Then we can join these table value function and table with in the help of "Cross Apply" as
   

   select e.ID,e.Name,e.City,x.Salary from Emp e left join
   (
   select ID,Name,Salary from Emp cross apply [dbo].fn_Salary(ID)
   )x on x.ID=e.ID

And show Result as