Difference between Inner Join VS Inner Remote Join

Question

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 :

and with simple Inner Join I got :

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

@jarlh I am using `MS SQL Server 2012` – Sunil Kumar Jul 27 '16 at 07:26 — Sunil Kumar, Jul 27 '16 at 07:26

Martin Smith · Accepted Answer · 2016-07-27T08:00:02.157

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

Thanks martin for your efforts..) – Sunil Kumar Jul 27 '16 at 08:30 — Sunil Kumar, Jul 27 '16 at 08:30

score 2 · Answer 2 · answered Jul 27 '16 at 07:24

2

You should try to use REMOTE only if the right table is on the Remote server and left is local. Also REMOTE should be used only when the left table has fewer rows than the right table.These are documented at https://msdn.microsoft.com/en-us/library/ms173815.aspx

answered Jul 27 '16 at 07:24

Madhivanan

13,470
1
24
29

Is it needed if both databases provide column distribution statistics? – jarlh Jul 27 '16 at 07:27

score 1 · Answer 3 · answered Jul 27 '16 at 07:26

If both tables are local the "remote" hint shouldn't change anything.

In your case the difference in the execution plan comes from the columns used in the joins:

P.UserId=U.UserId

vs

P.fdUserId=U.fdUserId

Without knowing your schema, I would say that the columns in the second query aren't indexed and so require a Sort operation as they are retrieved using the order on the clustered index.

score 0 · Answer 4 · answered Jul 27 '16 at 07:32

0

Inner Remote join and inner join show the same result, But i suggest use Inner join instead of Inner Remote. Test it many times, it returns the same.

answered Jul 27 '16 at 07:32

Vijunav Vastivch

4,153
1
16
30

score 0 · Answer 5 · answered Jul 27 '16 at 07:32

0

Remote Join

This is useful when the left table is a local table and the right table is a remote table.
REMOTE should be used only when the left table has fewer rows than the right table

answered Jul 27 '16 at 07:32

Jawad Siddiqui

195
1
1
15

Difference between Inner Join VS Inner Remote Join

5 Answers5