Join Tables from Different Databases in SQL Server

Here is a template that you can use to join tables from two different databases in SQL Server:

SELECT 
table_1.*,
table_2.*
FROM [Database_1].[Table_Schema].[Table_Name_1] table_1
JOIN [Database_2].[Table_Schema].[Table_Name_2] table_2 ON table_1.id = table_2.id

In the next section, you’ll see how to join two tables from two different database in SQL Server.

The tables and databases will be created under the same server.

Steps to Join Tables from Different Databases in SQL Server

Step 1: Create the first database and table

To start, create the first database called Database_1:

CREATE DATABASE Database_1

Next, create a table called ‘PersonName‘ (with a dbo schema):

CREATE TABLE Database_1.dbo.PersonName (
	PersonID int primary key,
	FirstName nvarchar(50),
	LastName nvarchar(50),
	AgeID int
)

Then, insert 5 records into the ‘PersonName’ table:

INSERT INTO Database_1.dbo.PersonName (PersonID,FirstName,LastName,AgeID)

VALUES

(1,'Bill','Smith',1005),
(2,'Mary','Davis',1009),
(3,'Martin','Green',1006),
(4,'Rob','Lee',1003),
(5,'Maria','Wilson',1007)

Finally, check if the records were inserted to the table:

SELECT * FROM Database_1.dbo.PersonName

As you can see, the 5 records are now present in the ‘PersonName’ table:

Join Tables from Different Databases in SQL Server

Step 2: Create the second database and table

Now create the second database called Database_2:

CREATE DATABASE Database_2

Then, create a table called ‘PersonAge‘ (with a dbo schema):

CREATE TABLE Database_2.dbo.PersonAge (
	AgeID int primary key,
	Age int
)

Insert 10 records into the ‘PersonAge’ table as follows:

INSERT INTO Database_2.dbo.PersonAge (AgeID,Age)

VALUES

(1000,20),
(1001,25),
(1002,30),
(1003,35),
(1004,40),
(1005,45),
(1006,50),
(1007,55),
(1008,60),
(1009,65)

Verify that the records were inserted into the table:

SELECT * FROM Database_2.dbo.PersonAge

You would see 10 records in the table:

Table

Step 3: Join the tables from the different databases in SQL Server

You can use the template below to join tables from two different databases:

SELECT 
table_1.*,
table_2.*
FROM [Database_1].[Table_Schema].[Table_Name_1] table_1
JOIN [Database_2].[Table_Schema].[Table_Name_2] table_2 ON table_1.id = table_2.id

Make sure to specify the database names, table schemas and table names when joining the tables from your different databases.

For the example reviewed, let’s use a left join to join the ‘PersonName’ table (from Database_1) with the ‘PersonAge’ table (from Database_2) using the AgeID field.

SELECT
pr.*,
ag.*
FROM [Database_1].[dbo].[PersonName] pr
LEFT JOIN [Database_2].[dbo].[PersonAge] ag ON pr.AgeID = ag.AgeID

In green, you’ll now see all the records and fields from the ‘PersonName’ table (under Database_1), while in blue you’ll get all the records and fields from the ‘PersonAge’ table (under Database_2):

How to Join Tables from Different Databases in SQL Server

Step 4 (optional): Drop the databases created

After completing the above exercise, you may choose to drop the two databases.

To drop Database_1 use:

DROP DATABASE Database_1

And to drop Database_2 use:

DROP DATABASE Database_2