SQL SERVER – How to Join a Table Valued Function with a Database Table

SQL
14 Comments

Just another day I received following question and I find it very interesting. I decided to try it out on SQL Server 2016’s new WideWorldImporters database. The question was – “How to Join a Table Valued Function with a Database Table?” This is indeed very interesting as this particular feature was introduced in SQL Server 2008, so what you will see in this blog post applies to every version of SQL Server after SQL Server 2008.

SQL SERVER - How to Join a Table Valued Function with a Database Table tablevaluedfunctions-800x409

Solarwinds

In database WideWorldImporters we have a table valued function – [Application].[DetermineCustomerAccess], which accepts city id as an input and returns us result if that particular city have accessed enabled or not. We will join this to Sales.Customers table which also have a column city. Now we want to know if the customer city has access enabled or not. To find out we have to join a table valued function with our customer table. Here is the syntax to join table valued function to a table. We will apply CROSS APPLY to connect function and table.

USE WideWorldImporters
GO
SELECT c.CustomerName, a.AccessResult
FROM Sales.Customers c
CROSS APPLY [Application].[DetermineCustomerAccess] (DeliveryCityID) a
GO

Let me know if you have any question in the comment area.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Case of Different Default Collation on Two Servers
Next Post
PowerShell – Reading Tables Data Using Script

Related Posts

14 Comments. Leave new

  • Arnab Roy Chowdhury
    September 23, 2016 9:26 am

    We can do normal inner join table value function. CROSS APPLY is where we don’t have any join condition for example, we have table1 and table2. table1 has a column called rowcount. For each row from table1 we need to select first rowcount rows from table2, ordered by table2.id

    Reply
  • José Pablo Valcárcel Lázaro
    September 23, 2016 10:09 am

    Hi Pinal. I suscribed your blog from long time ago and I’m curious about this post because this is the first time I see a function like this CROSS APPLY [Application].[DetermineCustomerAccess] (DeliveryCityID) on a SQL query. Is this a property of sql server databases or can be found in another databases like mysql/oracle?

    Regards and thanks for your article. Sorry if my question is silly.

    Reply
  • Hi,

    You can’t use the normal inner join if you had to pass the parameter to the function from the first table.

    Reply
  • Hi Pinal,
    I’ve been playing around with CROSS APPLY and experienced performance issues. When I rewrote my code using a SP with temporary tables, it got much quicker – like 90 seconds with CROSS APPLY to 3 seconds with the SP. Are functions generally slower than procedures?

    Regards
    Nils

    Reply
  • Cross and Outer apply used with table valued function. Its behave like Inner and outer join respectively. Below is query example to make it clear.

    IF(OBJECT_ID(‘dbo.emp’)) IS NOT NULL
    DROP TABLE emp
    create table emp
    (
    EmpID int,
    EmpName varchar(20),
    DepID int
    )
    INSERT INTO emp
    SELECT 1,’Rikhil’,1
    UNION ALL
    SELECT 2,’Mehul’,2
    UNION ALL
    SELECT 3,’Neeraj’,NULL
    GO
    IF(OBJECT_ID(‘dbo.Dep’)) IS NOT NULL
    DROP TABLE Dep
    create table Dep
    (
    DepID int,
    DepName varchar(20)
    )
    INSERT INTO Dep
    SELECT 1,’IT’
    UNION ALL
    SELECT 2,’Mechanical’
    UNION ALL
    SELECT 3,’Electrical’
    GO
    IF(OBJECT_ID(‘dbo.GetEmpDep’)) IS NOT NULL
    DROP FUNCTION GetEmpDep
    GO
    CREATE FUNCTION GetEmpDep(@DepID int)
    RETURNS TABLE
    AS
    RETURN
    select DepID ,
    DepName
    from Dep
    WHERE DepID=@DepID
    GO
    select ‘CROSS APPLY behave Like Inner join’ AS Title,*
    from emp
    CROSS APPLY GetEmpDep(DepID)

    select ‘OUTER APPLY behave Like Outer join’ AS Title,*
    from emp
    OUTER APPLY GetEmpDep(DepID)

    Reply
  • Thank you. :)

    Reply
  • Jesus cortes
    June 14, 2018 4:59 am

    thank you

    Reply
  • vinod chavan
    July 30, 2018 9:30 pm

    Hi , Pinal,
    i want to first value , middle value and last value in datetime column, im getting first and last by using min n max function, but im not getting middle value ie in between 12-1 pm
    im using it for attendance project if middle punch is not there then it should be null but

    pleaes do needful

    Reply

Leave a Reply

Menu