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