How to perform a join on the linked server side?

Question

SQL

Hello gentlemen.
It is necessary to join two tables, and one of them is located on a different server.
Sample example:

SELECT c.id AS card_id
, cmo.SaleID AS sale_user_name
, c.vin
FROM  dbo.Cards AS c 
INNER JOIN SQL11.Clients.dbo.cmOrders AS cmo 
ON cmo.VIN = c.vin
       WHERE(cmo.id =(SELECT MAX(id) 
        FROM SQL11.Clients.dbo.cmOrders AS cmo2
          WHERE      (VIN = c.vin)))

Yes, such a crooked query...
I heard somewhere that in such queries you can explicitly specify on which side to execute the join. In this case, it is necessary that it be on the 11th server, because the table is very large, and it makes no sense to drive it back and forth once again.
Are there any thoughts?

Answer 1

use REMOTE in INNER JOIN
technet.microsoft.com/en-us/library/ms173815.aspx

Answer 2

Or through f-yu OPERNROWSET.

SELECT f.* FROM OPENROWSET( 'SQLNCLI', ' Server='Server.domain.local; Trusted_Connection=yes;,
     'SELECT column1, column2 FROM remotedb.schemaname.tablename WHERE column1 IS NOT NULL