Can we use join for two different database tables?

Question

Can we use the join operation for two tables from different databases? If yes, how do I do it?

Both databases are on the same server and DBMS is the same.

Teflon

Answer 1

SQL Server allows you to join tables from different databases as long as those databases are on the same server. The join syntax is the same; the only difference is that you must fully qualify table names.

Let's suppose you have two databases on the same server - Db1 and Db2. Db1 has a table called Clients with a column ClientId and Db2 has a table called Messages with a column ClientId (let's leave asside why those tables are in different databases).

Now, to perform a join on the above-mentioned tables you will be using this query:

select *
from Db1.dbo.Clients c
join Db2.dbo.Messages m on c.ClientId = m.ClientId

Infer

It should perhaps be noted that both databases must be accessible in the same security context. In other words, if you log in with different credentials for the two databases then the above won't work. In that case you would probably have to use "Linked Servers". – Salamone

As @IanGoldby mentioned, you can use Linked Servers. When you link servers it is possible to define the authentication method - use user context or use credentials configured just for the link. – Massasauga

Answer 2

You could use Synonyms part in the database.

Then in view wizard from Synonyms tab find your saved synonyms and add to view and set inner join simply.

Claxton