Sunil Kumar Sunil Kumar - 4 months ago 25
SQL Question

Difference between Inner Join VS Inner Remote Join

I don't know where I have to ask this question, I just want to know what is the difference between

Inner Join
and
Inner Remote Join
?
I have just tried implemented both joins like below:

With Remote Inner Join


SELECT P.CompanyName,u.UserName from tb_Offices AS P
INNER REMOTE JOIN
tb_Users AS U
on P.UserId=U.UserId


With Simple Inner Join


SELECT P.CompanyName,u.UserName from tb_Offices AS P
INNER JOIN
tb_Users AS U
on P.UserId=U.UserId


Both Queries returns same kind of records.

Then I tried run with Execution plan that is :

With
Remote Inner Join
I got :

enter image description here

and with simple
Inner Join
I got :

enter image description here

I am not so much friendly with
SQL Execution Plan
.

I just wanted to know which is better between
Inner Join
and
Inner Remote Join
.

Thanks

Answer

If you look at the messages tab you will see

Warning: The join order has been enforced because a local join hint is used.

When you use the INNER REMOTE JOIN hint you are forcing the tables to be joined in the order as written rather than allowing it to explore all possible join permutations.

So a similar result to specifying OPTION (FORCE ORDER)

This explains the different execution plans.

This hint is not intended to be used for local tables.

An example where inadvertently forcing the join order this way would be extremely negative is below - as it forces the large tables A and B to be joined first before eliminating all rows with the join on C. When the optimiser is not constrained (first plan below) it reorders things to (C x A) x B and the plan is much more efficient.

CREATE TABLE #A(X INT PRIMARY KEY);
CREATE TABLE #B(X INT PRIMARY KEY);
CREATE TABLE #C(X INT PRIMARY KEY);

INSERT INTO #A 
SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY @@SPID) FROM master..spt_values v1, master..spt_values v2

INSERT INTO #B 
SELECT * FROM #A

SELECT *
FROM #A INNER JOIN #B ON #A.X = #B.X 
        INNER JOIN #C ON #A.X = #C.X 

SELECT *
FROM #A INNER REMOTE JOIN #B ON #A.X = #B.X 
        INNER JOIN #C ON #A.X = #C.X 

DROP TABLE #A, #B,#C

enter image description here

Comments