Use a subselect within a "locate" function to test for multiple values? Alternatives?-CodePudding

I am looking for a way to run a "locate" function on multiple values based on a subselect; this is the pseudocode I'm envisioning (which does not run, because the subselect returns more than one value; which is what I want).

select * from table
where locate((select distinct field1 from subquery), field2) > 0

This is an unknown number of values, so I cannot use "or" statements for multiple values.

The only way I can think to do it is to do a join on the table to the subselect, but I am worried about efficiency with this method.

with cte_subselect as (select distinct field1 from subquery)
select * from table inner join cte_subselect on 1=1
where locate(field1, field2) > 0

Is the inner join method my only option?

CodePudding user response：

You want to combine all results from the subquery in with the rows of the main table and search for matches. In short, you want to filter over the cross product of a table with a subquery.

The typical solution is to do what you are doing already. Namely:

select t.*
from t
join (select distinct field1 from subquery) x
  on locate(x.field1, t.field2) > 0

Now, if performance is important you can speed it up by adding an index:

create index ix1 on t (field2);

Then, the query can be rephrased as:

select t.*
from (select distinct field1 from subquery) s
join t on t.field2 like s.field1 || '%'

CodePudding user response：

Try this instead of inner join.

with cte_subselect as (select distinct field1 from subquery)
select * from table
where  (select max(locate(field1, field2) ) from cte_subselect)  > 0