MySQL 8.0

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

create table horoscope(id int, sur varchar(10), fam varchar(10), horoscope varchar(10))
create table customer(id int, sur varchar(10), fam varchar(10))
insert into customer values(1,'John', 'Doe'),(2,'Jack', 'Doe'),(3,'Lisa', 'Smith'),(4,'Carl', 'Smith')
SELECT * FROM customer


id
sur
fam


1
John
Doe

2
Jack
Doe

3
Lisa
Smith

4
Carl
Smith


insert into horoscope values(1,'John', 'Doe', 'text1'),(2,'Jane', 'Doe', 'text2'),(3,NULL, 'Doe', 'text3'),(4,'Ike', 'Smith', 'text4'),(5,NULL, 'Smith','text5')
SELECT * FROM horoscope


id
sur
fam
horoscope


1
John
Doe
text1

2
Jane
Doe
text2

3
null
Doe
text3

4
Ike
Smith
text4

5
null
Smith
text5


select c.sur, c.fam, h.horoscope from customer c 
inner join horoscope h
on (c.fam = h.fam and c.sur = h.sur) or 
  (c.fam = h.fam and h.sur is null and not exists(
    select 1 from horoscope 
    where fam = c.fam and sur = c.sur
  )
)


sur
fam
horoscope


John
Doe
text1

Jack
Doe
text3

Lisa
Smith
text5

Carl
Smith
text5

id	sur	fam	horoscope
1	John	Doe	text1
2	Jane	Doe	text2
3	null	Doe	text3
4	Ike	Smith	text4
5	null	Smith	text5