Implementing an Outer Join
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