Search code examples
sqlpostgresqljoin

Query using ILIKE with IN

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?

Solution

  • 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 || '%')