Q:

How to join 2 tables from 2 different schemas but within same database

I have 3 tables namely Customer_1,cust_accts which belongs to Schema_1 and Customer_2 which belongs to Schema_2.All the tables are in the same database.
I have a sql query like this 
declare

l_cust_id customer_1.customer_id%type;

begin
select customer_id into l_cust_id
from cust_accts
where 
customer_number='';

insert into Customer_2
(
 column_1,
 column_2,
)
select
d1.column_1,
d1.column_2
from
customer_1 d1
where d1.customer_id=l_cust_id;
commit;
end;

Now my question here is where all i should put schema names in front of these tables, like schema_1.customer_1 like this where all i have to put. And Please not all the necessary db links, connections are already given to access these tables.
Thanks in advance for your answers.

A:

The below sql worked perfectly:
select count(*)
from SCHEMA1.TABLE1 ST1
INNER JOIN SCHEMA2.TABLE2 ST2 ON ST1.COMMON_FIELD=ST1.COMMON_FIELD;

A:

Assuming you don't have any synonyms and have the necessary privileges granted, you need to specify the schema for any objects that are not in your current schema. If you're connected as Schema_1 then you'd need to prefix the objects owned by Schema_2:
insert into Schema_2.Customer_2 (...)
select ... from customer_1 d1
where ...

If you're connected as Schema_2 then you'd need to prefix the objects owned by Schema_1:
insert into Customer_2 (...)
select ... from Schema_1.customer_1 d1
where ...

('Connected as' is a simplification that will suffice if you're new I think; you can change your current schema within a session but that will just be confusing now!)
If you want the exact same SQL to be valid for either user, or any other user with the right privileges, then you can prefix all objects:
insert into Schema_2.Customer_2 (...)
select ... from Schema_1.customer_1 d1
where ...

But then it would perhaps be simpler to manage by wrapping in a procedure.
The documentation has more on schema, objects, references and name resolution.

