MySQL 8.0

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

create table Empl (id int primary key, firstname varchar(8), lastname varchar(8))
insert into Empl values (1, 'fname1', 'lname')
insert into Empl values (2, 'fname2', 'lname')
insert into Empl values (3, 'fname3', 'lname3')
with t as (
    select e.*, count(*) over(partition by LastName) as num
    from Empl e)
select * from t where num>1


id
firstname
lastname
num


1
fname1
lname
2

2
fname2
lname
2


select e1.*
from Empl e1
where exists (
    select null 
    from Empl e2 
    where e2.LastName = e1.LastName and e2.id != e1.id)


id
firstname
lastname


1
fname1
lname

2
fname2
lname