Oracle Cross-Database Joins: Complete Guide

1 year ago
Liam
2 minutes

In Oracle, there are several methods available to achieve cross-database joins.

  1. Create a Database Link: First, create a database link in the target database, and then use this link to execute queries in the current database. For example, if you have two databases, A and B, and need to query data from database B in database A, you can create a database link in database A that points to database B, and then use this link to execute the query.
-- 在数据库A中创建数据库链接
CREATE DATABASE LINK B_LINK CONNECT TO username IDENTIFIED BY password USING 'B';

-- 在数据库A中使用链接查询数据库B的数据
SELECT * FROM table@B_LINK;
  1. Using External Table: Create an external table in the target database, map the data from the target database to the external table, and then query the external table in the current database to retrieve the data from the target database. For example, if there are two databases A and B, and you need to query the data from database B in database A, you can first create an external table in database B pointing to the target table, and then query the external table in database A.
-- 在数据库B中创建外部表
CREATE TABLE external_table (
  column1 datatype,
  column2 datatype,
  ...
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY directory_name
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY newline
    FIELDS TERMINATED BY ',' (
      column1,
      column2,
      ...
    )
  )
  LOCATION ('data_file.csv')
)
REJECT LIMIT UNLIMITED;

-- 在数据库A中查询数据库B的数据
SELECT * FROM external_table;
  1. Using a Union All query: merge the data from tables in two databases by using a Union All operation, and then query the merged result in the current database. For example, if we have databases A and B, and need to query the data from database B in database A, we can first query the tables in database A, and then merge the tables in database B using a Union All operation into the query result.
-- 在数据库A中查询数据库A的数据
SELECT * FROM tableA

UNION ALL

-- 查询数据库B的数据
SELECT * FROM tableB;

It is important to note that the database connections and external tables mentioned above need to be created and configured in the corresponding database, and require the appropriate access permissions between databases. Additionally, cross-database join queries may have an impact on system performance, so it is recommended to consider performance and security factors in practical applications.

bannerAds