How to join two tables if they are in different schemas

Question

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

Do you mean you want to join two tables from two different SQL databases? or two different servers? — Ben, Apr 28 '11 at 04:28
Possible duplicate of [Query two tables from different schema](http://stackoverflow.com/questions/980620/query-two-tables-from-different-schema) — Viktor Mellgren, Nov 04 '16 at 08:06
My two cents... The comma on the third line should probably be JOIN. — DanimalReks, Aug 27 '19 at 13:50

score 9 · Answer 1 · answered Apr 28 '11 at 04:43

9

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...

answered Apr 28 '11 at 04:43

gbn

422,506
82
585
676

2

@Greg Randall: clarity, less chance to feck up, filter/join separation, semantically correct with OUTER JOINS, later ANSI standard (1992), deprecated `*=` and `=*` (OUTER JOIN-in-WHERE) syntax... http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338 – gbn Apr 28 '11 at 04:56
Yeah the only time I use it is for outer joins. There isn't a performance difference, I just find it easier to read in ANSI 89, even if it makes my code more error prone. – Greg Randall Apr 28 '11 at 20:46

score 4 · Answer 2 · edited Oct 26 '12 at 15:00

4

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.

edited Oct 26 '12 at 15:00

Jude Fisher

11,138
7
48
91

answered Oct 26 '12 at 14:34

Anthony

79
5

score 3 · Answer 3 · answered Apr 28 '11 at 04:52

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

How to join two tables if they are in different schemas

3 Answers3

Linked