Ketul Soni Ketul Soni - 1 year ago 84
SQL Question

SQL Server Query In Operator

I have this type of record:


but I want the above record to be changed like the record below.


As I want to use IN operator to get result.

Ben Ben
Answer Source

Note that using a junction table will usually perform better as noted in the comments.

Nevertheless, assuming you are stuck with the design:

ID      ValueList
1       Uno,Dos,Tres
2       Foo,Bar,Baz,Quux

And you want to do the equivalent of this:

Select * 
from TableA a
where @Value in ValueList -- ERROR

Try this:

Select * 
from TableA a
where ','+ValueList+',' like '%,'+@Value+',%'

If you want to do this:

select * 
from TableA b
where b.Value in (select ValueList from TableA a where a.ID = b.ID)


select * 
from TableB b
where exists (
    select 1 from TableA a 
    where a.ID = b.ID and ','+a.ValueList+',' like '%,'+b.Value+',%'

Notes on design and performance: This design prevents any index being used on the column ValueList. This may not be a problem if:

  • TableA is very small and has very few rows (e.g. < 10 rows). This is because if the data fits into one or two pages, the overhead involved with looking up the index may be greater than the overhead involved in just scanning the page and doing string comparisons.

  • Or only a very small subset of rows are actually being searched.

  • For example, if you are looking up individual rows by a unique key, or a few tens of rows by an efficient index, and just want to filter based on whether a string is in ValueList, this may be faster than a junction table, because the data is held in the same page.

  • It may also be faster than filtering client-side (because rows which fail the test don't have to be returned to the client).

In other words, if you are not searching by values from this list, but merely filtering by them, it may not be worth putting them in to a junction table.

As always one should not be dogmatic about design, but test.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download