How to use LIKE wild card on subquery?

Question

Table: FirstNames
NAME
    Tom
    Joe
    Peter

Table: FullNames
FULL_NAME:
    Tom Petty.
    Joe Satriani.
    Peter Griffin.
    Sarah Connor.

I would like to run a query:

select * 
from FullNames where FULL_NAME like '%' || (select NAME from FirstNames) || '%'

It yields:

ORA-01427: single-row subquery returns more than one row

which seems correct. Is there a way to do that in Oracle?

Answer 1

You could use JOIN:

SELECT * 
FROM FullNames f
JOIN FirstNames g
  ON f.FULL_NAME LIKE '%' || g.NAME || '%';

Answer 2

You can use exists:

select f.*
from FullNames f
where exists (select 1
              from firstnames fn
              where f.FULL_NAME like '%' || fn.NAME || '%'
             );