Join Tables from Different Databases in SQL Server

To join tables from two different databases in SQL Server (under the same server):

SELECT 
tb_1.*,
tb_2.*
FROM [database_1].[table_schema].[table_name_1] tb_1
JOIN [database_2].[table_schema].[table_name_2] tb_2 ON tb_1.id = tb_2.id

The Steps

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 ‘products‘ (with a dbo schema):

CREATE TABLE database_1.dbo.products (
	product_id int primary key,
	product_name nvarchar(50)
)

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

INSERT INTO database_1.dbo.products (product_id, product_name)

VALUES

(1, 'Laptop'),
(2, 'Tablet'),
(3, 'Monitor'),
(4, 'Printer'),
(5, 'Keyboard')

Finally, check that the records were inserted into the table:

SELECT * FROM database_1.dbo.products

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

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 ‘prices‘ (with a dbo schema):

CREATE TABLE database_2.dbo.prices (
	product_id int primary key,
	product_price int
)

Insert 5 records into the ‘prices’ table as follows:

INSERT INTO database_2.dbo.prices (product_id, product_price)

VALUES

(1, 1200),
(2, 300),
(3, 500),
(4, 150),
(5, 80)

Verify that the records were inserted into the table:

SELECT * FROM database_2.dbo.prices

You would see 5 records in the 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 
tb_1.*,
tb_2.*
FROM [database_1].[table_schema].[table_name_1] tb_1
JOIN [database_2].[table_schema].[table_name_2] tb_2 ON tb_1.id = tb_2.id

For example, use a left join to join the ‘products’ table (from database_1) with the ‘prices’ table (from database_2) using the product_id field:

SELECT 
tb_1.*,
tb_2.*
FROM [database_1].[dbo].[products] tb_1
LEFT JOIN [database_2].[dbo].[prices] tb_2 ON tb_1.product_id = tb_2.product_id

In yellow, you’ll now see all the records and fields from the ‘products’ table (under database_1), while in green you’ll get all the records and fields from the ‘prices’ table (under database_2):

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