Join Tables from Different Databases in SQL Server

Here is the general syntax that you may use to join tables from two different databases in SQL 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

In the next section, you’ll see how to join two tables from two different databases 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 ‘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 if 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:

product_id	product_name
1	Laptop
2	Tablet
3	Monitor
4	Printer
5	Keyboard

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:

product_id	product_price
1	1200
2	300
3	500
4	150
5	80

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

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 ‘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):

product_id	product_name	product_id	product_price
1	Laptop	1	1200
2	Tablet	2	300
3	Monitor	3	500
4	Printer	4	150
5	Keyboard	5	80

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