For an instructor lead, in-depth look at learning SQL click below.
If you’ve worked with SQL for a while, you know how powerful and convenient it is to manipulate data by joining tables within one database. But what if you need to join tables from different databases? Fortunately, SQL also provides the capability to do this, and that’s the focus of our topic today.
The Basic Concept: Full Qualified Name
In SQL, each table is identified by a fully qualified name composed of four parts: the server name, the database name, the schema name, and the table name, usually written as:
|
1 |
// [server].[database].[schema].[table] |
Normally when you write SQL queries, you are working with tables in the same database so you do not need to specify the server, database, and schema. However, if you want to join tables from different databases, you need to use these fully qualified names.
Joining Tables from Different Databases
Let’s pretend we have two databases, Sales and HR, hosted on the same SQL server. The Sales database has a table named Orders and the HR database has a table Employees. Here’s how you might write a query to join these two tables:
|
1 2 3 4 5 6 |
SELECT O.OrderId, O.OrderDate, E.EmployeeName FROM Sales.dbo.Orders as O INNER JOIN HR.dbo.Employees as E ON O.EmployeeId = E.EmployeeId |
In the script above, ‘Sales.dbo.Orders’ and ‘HR.dbo.Employees’ are the fully qualified names of the tables. In each database, ‘dbo’ is the default schema. ‘O’ and ‘E’ are alias names for the Sales.dbo.Orders and HR.dbo.Employees, which simplifies writing the rest of the query. Lastly, the join is made on EmployeeId.
Joining Tables from Different SQL Servers
Some situations call for updating data across multiple database servers. Though the idea of joining tables across servers might seem daunting, SQL Server provides linked servers that allow us to fetch records from a remote server. We create a linked server, assigned a name (alias) of the remote server, and then use that alias name in our queries.
For example, if you have a linked server ‘RemoteServer’, it’s possible to join tables from the local and remote server as shown:
|
1 2 3 4 5 6 |
SELECT L.LocalColumn, R.RemoteColumn FROM LocalDB.dbo.LocalTable as L INNER JOIN RemoteServer.RemoteDB.dbo.RemoteTable as R ON L.CommonID = R.CommonID; |
In this scenario, the local and remote tables are joined on ‘CommonID’.
Conclusion
Joining tables across databases or servers leverages the full potential of SQL, enabling us to manipulate, analyze, and report data flexibly and effectively. These techniques might seem tricky at first, but with practice, you’ll find they open up a whole new world of possibilities in SQL. Happy querying!