MySQL 8.0

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

create table t1(id int, name varchar(20))
insert into t1 values(1, 'Name1')
create table t2 (id int, job varchar(20))
insert into t2 values(1, 'Job1')
insert into t2 values(1, 'Job2')
insert into t2 values(1, 'Job3')
select distinct t1.id, t2.id
from t1
left join t2 on t1.id = t2.id


id
id


1
1


select distinct t1.id, t2.id, t2.job
from t1
left join t2 on t1.id = t2.id


id
id
job


1
1
Job3

1
1
Job2

1
1
Job1