PostgreSQL subquery using like

Question

lets say i have first table like this

Branch table

|name     |description|
|123456ABC|FOO        |
|553646DEF|FO2        |

and second table like this

Balance table

|name|description|
|ABC |oof        |
|DEF |2of        |

i want to query to Balance table, where each row containing name from Branch table.. for example "123456ABC" in Branch table, i want to get "ABC" row from Balance table

how could i achieve that? i've tried this query so far with no luck

select * from Balance
where name like (
        SELECT `name` FROM Branch
);

any suggestion?

Answer 1

You should convert the balance's names to LIKE patterns:

SELECT * FROM Balance
WHERE (
    SELECT `name` FROM Branch
) LIKE '%' || name;

A join may look more readable:

SELECT b.* FROM Balance b JOIN Branch r ON r.name LIKE '%' || b.name;

Answer 2

I don't know if you will have dupes or not, so you may want to consider using a semi-join. For large datasets, a semi-join will typically be more efficient than an in-list query.

@clemens solution looks good, assuming no dupes. Alternatively, you can use regex:

select *
from balance ba
where exists (
  select null
  from branch br
  where
    br.name ~ ba.name
)

Performance-wise, I think like will outperform the regex, but it's an option.

Also, if your string is always at the end, you can consider a join using right or substr:

select *
from balance ba
where exists (
  select null
  from branch br
  where
    right (br.name, length (ba.name)) = ba.name
)