CODEWITHSUNDEEP
sqlpostgresqljoin
Utopia025
Utopia025

Reputation: 1201

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?

Upvotes: 8

Views: 22169

Answers (2)

anish
anish

Reputation: 11

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

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658072

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

Upvotes: 6

Related Questions