MySQL 8.0

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

create table department (
  id varchar(100) not null primary key,
  name varchar(100)
)
create table employee (
  id varchar(100) not null primary key,
  name varchar(100),
  fk varchar(100)
)
insert into department (id, name) values
('d1', 'programming'),
('d2', 'designing'),
('d3', 'accounting')

Records: 3  Duplicates: 0  Warnings: 0


insert into employee (id, name, fk) values
('e1', 'john', 'd1'),
('e2', 'jane', 'd2'),
('e4', 'zack', null)

Records: 3  Duplicates: 0  Warnings: 0


select l.*, r.*
from employee as l
left join department as r on l.fk = r.id

union all

select null, null, null, r.*
from department as r
where not exists (
  select 1
  from employee as l
  where l.fk = r.id
)


id
name
fk
id
name


e1
john
d1
d1
programming

e2
jane
d2
d2
designing

e4
zack
null
null
null

null
null
null
d3
accounting

id	name	fk	id	name
e1	john	d1	d1	programming
e2	jane	d2	d2	designing
e4	zack	null	null	null
null	null	null	d3	accounting