Query using ILIKE with IN

Question

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

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

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

Answer 1

You are considering another angle than you need. It might be easier:

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

I use only sub-selections to improve performance. Not sure if the query planner is smart enough to use the same plan with this simpler request (and your version of Postgres):

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

Perhaps you can try (with EXPLAIN ANALYZE ) and let them know if they use the same plan (and execute it)?

And I suspect that you really need to add % for your purpose:

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