Export (0) Print
Expand All

Implementing an Outer Join

SQL Server 2014

Outer join is not supported in natively compiled stored procedures. The following sample shows a way to implement the functionality of a left outer join in a natively compiled stored procedure.

The samples uses a table variable to simulate a cursor on the left side of the join, and a table variable to construct a single result set, which is only suitable when processing a limited number of rows as it involves creating an additional copy of the data rows.

A variable (@outer) of type t1_type is used to iterate over the rows from t1, using a while loop to simulate a cursor. The variable @result of type t1t2_join_type is then used to construct the result set.

You should test the performance of this workaround, to be sure that it performs as expected in your application.

-- original query:
select 
   t1.c1 as t1c1,
   t1.c2 as t1c2,
   t2.c2 as t2c2,
   t2.c3 as t2c3
   from t1 left join t2 on t1.c2=t2.c3
GO

create table dbo.t1
(c1 int not null primary key nonclustered,
c2 int not null) with (memory_optimized=on)

create table dbo.t2
(c2 int not null primary key nonclustered,
c3 int not null) with (memory_optimized=on)

INSERT t1 VALUES (1,2)
INSERT t1 VALUES (2,3)
INSERT t1 VALUES (3,2)
INSERT t2 VALUES (2,3)
INSERT t2 VALUES (4,3)
GO

create type dbo.t1_type as table
(
   id int identity not null primary key nonclustered hash with (bucket_count=1024),
   c1 int,
   c2 int
) with (memory_optimized=on)
GO


create type dbo.t1t2_join_type as table
(
   t1c1 int not null index ix_t1c1,
   t1c2 int not null,
   t2c2 int,
   t2c3 int
) with (memory_optimized=on)
GO


-- ====== scenario: generic left join

-- stored procedure including the workaround
create procedure dbo.usp_left_join
with native_compilation, execute as owner, schemabinding
as
begin atomic with (transaction isolation level = snapshot, language = N'us_english')

   DECLARE @outer dbo.t1_type
   DECLARE @result dbo.t1t2_join_type


   -- populate the variable used for iterating over the outer rows
   INSERT @outer(c1, c2) select c1,c2 from dbo.t1

   DECLARE @i int = 1
   DECLARE @max int = scope_identity()

   DECLARE @t1c1 int
   DECLARE @t1c2 int

   while @i <= @max
   begin   
      select @t1c1 = c1, @t1c2 = c2 from @outer where id = @i

      INSERT @result select @t1c1, @t1c2, c2, c3 from dbo.t2 where c3 = @t1c2

      if @@rowcount = 0 
         INSERT @result (t1c1, t1c2) VALUES (@t1c1, @t1c2)

      set @i += 1
   end

   select 
      t1c1,
      t1c2,
      t2c2,
      t2c3
   from @result
end
GO

exec dbo.usp_left_join
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft