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.
Then in view wizard from Synonyms tab find your saved synonyms and add to view and set inner join simply.