Issue with the SQL query while doing string_split

Question

Trying with this query

select * 
from mytable
JOIN STRING_SPLIT('109,110,111,114',',')
ON value = (select col1 from mytable WHERE id = 52);

I am getting empty results, why it is not matching? The col1 has the values as comma separated stored.

Answer 1

Just use an on clause, no subquery:

select t.* 
from mytable t JOIN 
     STRING_SPLIT('109,110,111,114',',') s
     on s.value = t.col
where t.id = 52;

Answer 2

Why use STRING_SPLIT at all here? Wouldn't this be easier with a IN?

SELECT MT.* --This should really be a list of columns
FROM dbo.MyTable MT
WHERE MT.id = 52
  AND MT.col1 IN (109,110,111,114); --Assumes that col1 is an int

Answer 3

You want apply :

select *
from mytable mt cross apply
     string_split(mt.col1, ',') mt1 inner join
     string_split('109,110,111,114',',') spt
     on spt.value = mt1.col1
where mt.id = 52;