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?

Ten

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

Troglodyte

Answer 2

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

Larcenous

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Rubicund