What all joins do functions and tables support?

We can’t use normal joins (inner/left/ right joins) directly on table and function, however we can use cross apply/ outer apply on table and function. Cross apply will work as the inner join operator with the functions and tables.it will return the matching row between the table valued function and table. Outer apply will work as the full outer join on the function and table. It will show all the records which are matching in both the tables and which do not match. However, with the help of the where condition we can use this as left join/ right join.

Function to get the percentage of increment:

create FUNCTION dbo.udf_get_employs_per(

 @Salary DEC(10,2)

)RETURNS @inc table (per int)

as

begin

insert into @inc

Select case when @Salary < 2000>

when @Salary between 2000 and 2100 then 11

when @Salary between 2100 and 2200 then 10

when @Salary between 2200 and 2300 then 9

when @Salary > 2300 then 8

end

  return

end

Select * from employ inner join dbo.udf_get_employs_per on (EmploySalary) will give the error

SELECT EMPLOYID, EMPLOYNAME, EMPLOYSALARY [OLD_SALARY], PER, EMPLOYSALARY+ (EMPLOYSALARY*PER/100) [NEW_SALARY] FROM DBO.EMPLOY D CROSS APPLY [DBO].UDF_GET_EMPLOYS_PER (D.EMPLOYSALARY) U