How to join two tables if they are in different schemas

I have two different schemas in SQL Server (say S1, S2). And two tables in those schemas(say S1.Table1, S2.Table2). I want to query these two tables from schema S1.

Both S1 and S2 are in SQL Server 2005 databases. I want开发者_开发技巧 to do something like this:

select T1.Id
  from S1.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId

Use 3 part object names to specify the database: I assume you mean "database" not "schema" (in say the Oracle sense)

select T1.Id
from 
  DB1.schema.Table1 T1
 JOIN
   DB2.schema.Table2 T2 ON T1.Id = T2.refId

Note the better way of doing JOINs...

Select T1.Id

FROM

s1.Table1 T1

JOIN

s2.Table2 T2

WHERE

T1.Id = T2.refId;

This is the way to do your query on MySQL. I would assume it also works in Oracle 11g.

You didn't mention if the DBs were on the same server. If yes, you can follow the answer above. If not, you will need to create a linked server one of the servers, and then you can reference the linked server via

select T1.Id
  from [linkedservername].DB1.Schema.Table1 T1
     , S2.Table2 T2 
 Where T1.Id = T2.refId