Can we use join for two different database tables?

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.


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

You could use Synonyms part in the database.

enter image description here

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