Query using ILIKE with IN

Question

Is it possible to run a query using ILIKE with the IN function? For example:

SELECT store_names
FROM stores
WHERE states ILIKE IN (SELECT location
                       FROM   locations
                       WHERE  stateID = 1)

I want to run the results returned from the nested query through the ILIKE function. Is there a way to do this?

Answer 1

Can be simpler:

SELECT s.store_names, l.location
FROM   stores s
JOIN   locations l ON s.states ILIKE l.location
WHERE  l.stateid = 1

You can check the resulting query plan with EXPLAIN ANALYZE.

You may need to add leading and trailing % to get partial matches:

... ON s.states ILIKE ('%' || l.location || '%')