> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/query-concepts-and-performance/access-multiple-schemas-in-single-query.md).

# 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**.

{% hint style="info" %}
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.
  {% endhint %}

## 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:**

```sql
SELECT username FROM all_users;
```

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

2. #### Connect as both users and run

   ```sql
   SELECT global_name FROM global_name;
   ```

   Compare the result — same value → same database.
3. #### Run this from both users

   ```sql
   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:

```sql
SHOW DATABASES;
```

Then:

```sql
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:**

```sql
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:**

```sql
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`)**

```sql
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:

```sql
GRANT SELECT ON salaries TO hr_schema@HRDB;
```

Alternatively, to make it more generic:

```sql
GRANT SELECT ON salaries TO PUBLIC;
```

**4. Access the remote table via the link**

Back in `HRDB`, run:

```sql
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).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/query-concepts-and-performance/access-multiple-schemas-in-single-query.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.