Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table#1( first_name, last_name, role )
as
select 'John', 'Smith', 'Dev' from dual union all
select 'Jane', 'Doe', 'Tester' from dual union all
select 'Rock', 'Lee', 'Dev' from dual
;
3 rows affected

create table table#2 ( first_name, last_name )
as
select 'John', 'Smith' from dual union all
select 'John', 'Smith' from dual union all
select 'Jane', 'Doe' from dual union all
select 'Jane', 'Doe' from dual union all
select 'Rock', 'Lee' from dual ;
5 rows affected

select T1.first_name, T1.last_name, T1.role
from table#1 T1
  join table#2 T2
    on T1.first_name = T2.first_name and T1.last_name = T2.last_name
;


FIRST_NAME
LAST_NAME
ROLE


John
Smith
Dev

John
Smith
Dev

Jane
Doe
Tester

Jane
Doe
Tester

Rock
Lee
Dev

FIRST_NAME	LAST_NAME	ROLE
John	Smith	Dev
John	Smith	Dev
Jane	Doe	Tester
Jane	Doe	Tester
Rock	Lee	Dev