Access Multiple Schemas in Single Query

About

In SQL, a schema is a logical namespace within a database that contains tables, views, procedures, etc. There are scenarios where we need to query across multiple schemas—either within the same database or across different databases.

A schema is a logical container in a database that holds related objects like tables, views, and procedures, helping organize and manage them separately from other objects.

In some databases, schema and user can be the same - it depends on the database system.

In Oracle:

• A schema is tightly linked to a user.

• When we create a user, a schema with the same name is created automatically.

• So: User = Schema (in most practical terms).

In PostgreSQL:

• A user is an account that logs in.

• A schema is a namespace to organize objects.

• A user can own multiple schemas, and schemas can exist independently of users.

In MySQL:

• The term schema is often used interchangeably with database.

• So here, schema ≠ user.

How to check if 2 schema are in same database ?

To check if two schemas are in the same database, we need to understand how our RDBMS defines a "database" and a "schema", as this varies between systems.

If we can reference both schema1.table and schema2.table in a single query without using a database link, foreign wrapper, or database name prefix, they are most likely in the same database.

Oracle

• Oracle treats a user = schema.

• All schemas live under the same database (SID).

How to check:

1. Run this query to see available schemas:

SELECT username FROM all_users;

If we can access both schemas with one user session, they are in the same database.

1. Connect as both users and run

   SELECT global_name FROM global_name;

   Compare the result — same value → same database.

2. Run this from both users

   SELECT name, dbid FROM v$database;

   If both return the same values → same database.

MySQL

• MySQL uses the term schema = database.

• So two “schemas” are actually two separate databases.

How to check:

SHOW DATABASES;

Then:

SHOW TABLES FROM schema1;
SHOW TABLES FROM schema2;

If schema1 ≠ schema2, they are separate databases.

Case 1: Accessing Multiple Schemas in the Same Database

Scenario

We have two schemas:

• hr_schema (contains employees table)

• finance_schema (contains salaries table)

We are logged in as user hr_schema and want to join hr_schema.employees with finance_schema.salaries.

Problem

By default, hr_schema cannot access tables in finance_schema.

Step-by-Step Setup

1. Connect as finance_schema and grant privileges:

GRANT SELECT ON salaries TO hr_schema;

This gives hr_schema permission to read from finance_schema.salaries.

2. Connect as hr_schema and run the query:

SELECT 
    e.employee_id,
    e.name,
    f.salary
FROM 
    employees e
JOIN 
    finance_schema.salaries f
ON 
    e.employee_id = f.employee_id;

Notes

• We can access the other schema's table directly using schema_name.table_name.

• We only need SELECT privilege to read. For other operations like INSERT, UPDATE, or DELETE, grant the respective permissions.

Case 2: Accessing a Schema in a Different Database (Using DB Link)

Scenario

• hr_schema is in database HRDB.

• finance_schema is in database FINDB.

• We are logged in as hr_schema and want to access finance_schema.salaries which is in another Oracle database.

Problem

We cannot directly query across databases unless a database link is set up and permissions are granted.

Step-by-Step Setup

1. Ensure tnsnames.ora or Oracle Net connectivity is configured

Make sure FINDB is defined in our tnsnames.ora or our connection string points to the target database.

2. Create a database link in HRDB (as hr_schema)

CREATE DATABASE LINK finance_link
CONNECT TO finance_schema IDENTIFIED BY our_password
USING 'FINDB';

• finance_link is the name of the DB link.

• 'FINDB' is the Oracle Net service name pointing to the FINDB instance.

3. Grant required privileges in FINDB

Connect to finance_schema in FINDB and run:

GRANT SELECT ON salaries TO hr_schema@HRDB;

Alternatively, to make it more generic:

GRANT SELECT ON salaries TO PUBLIC;

4. Access the remote table via the link

Back in HRDB, run:

SELECT 
    e.employee_id,
    e.name,
    f.salary
FROM 
    employees e
JOIN 
    salaries@finance_link f
ON 
    e.employee_id = f.employee_id;

Notes

• Always use table_name@db_link_name to reference a remote object.

• We can create public or private database links:

  • Private: Available only to the user who created it.

  • Public: Available to all users (requires DBA privilege).