By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601479 fiddles created (47959 in the last week).
create table names (name varchar2 (8))
/
✓
hidden batch(es)
create table phones (name varchar2 (8), num varchar2 (16))
/
✓
hidden batch(es)
select n.name, t.num
from names n
left join (
select name, num
from phones p
where exists (select 1
from names n where n.name = p.name)
group BY name, num
having count(*) = 1) t on t.name = n.name
/
NAME
NUM
…
hidden batch(es)
alter session set events '22829 trace name context forever';
✓
hidden batch(es)
select n.name, t.num
from names n, lateral (
select num
from phones p
where p.name = n.name
group BY num
having count(*) = 1)(+) t
/
