Operators similar to 'in' that compares two sets

Question

I want to compare two sets of values and see whether there are intersections. The first set of values are specified at the runtime and the second set of values are stored in a row of a database.

Here are some sample code:

CREATE TABLE #tab1 (var1 INT, var2 INT , var3 INT)

INSERT INTO #tab1 
VALUES (1,2,3),(0,0,0),(0,4,0)

Select
a = (case when 1 IN (var1,var2,var3) OR
               2 IN (var1,var2,var3) OR
               3 IN (var1,var2,var3)
    THEN 1 else 0 end) 
from #tab1

I feel that the code is too cumbersome, and am wondering whether there are some simple way to do it. Thanks!

Answer 1

Given three set up inputs, and a separate table worth of values, the following code will work.

declare @input1 int =1
declare @input2 int =2
declare @input3 int =3

CREATE TABLE #tab1 (var1 INT, var2 INT , var3 INT)
INSERT INTO #tab1 
VALUES (1,2,3),(0,0,0),(0,4,0),(1,4,1),(4,0,0),(0,5,1),(2,4,0),(1,4,3)


 declare @set table(input int)
 insert  @set values (@input1), (@input2),(@input3);

 select distinct #tab1.*, case when join1.i1 is null then 'No Match' else 'match' end matching
 from #tab1
 left join (select distinct a.input i1, b.input i2 ,c.input i3
                from @set a 
                cross join @set b 
                cross join @set c ) join1
    on join1.i1=#tab1.var1
        or join1.i2=#tab1.var2
        or join1.i3=#tab1.var3

I think this is what you were looking for if you didn't want to use 'in'. To be honest, I would use 'in'. It is much simplier and easier to write. The above code took some time to make work. However, I would update your 'in' code as follows:

select * ,'match'
from #tab1
where @input1 in (var1, var2, var3)
    or @input2 in (var1, var2, var3)
    or @input3 in (var1, var2, var3)
union 
select * ,'no-match'
from #tab1
where @input1 not in (var1, var2, var3)
    and @input2 not in (var1, var2, var3)
    and @input3 not in (var1, var2, var3)

Answer 2

You can just JOIN to real or dynamically derived tables:

DECLARE @var1 INT=2;
DECLARE @var2 INT=15;
DECLARE @var3 INT=-7;

IF EXISTS (SELECT 1 FROM 
             (VALUES (1),(2),(3))tableNums(number)
             INNER JOIN (VALUES (@var1), (@var2), (@var3))tableVars(var)
             ON tableNums.number=tableVars.var
       )
    SELECT 1    --or do something else interesting
ELSE SELECT 0   --or do the opposite

If the list was very long, I'd probably create it in a #temp table in advance instead of doing it in the statement, but the JOIN and EXISTS logic would be the same.

---

Similarly, we could used INTERSECT instead of JOIN:

CASE WHEN EXISTS ( SELECT * FROM (VALUES (1), (2), (3)) AS x(i) 
                 INTERSECT 
                   SELECT * FROM (VALUES (@var1), (@var2), (@var3)) AS y(i)
                 )
THEN 1 ELSE 0 END 

Test at dbfiddle.uk.

Answer 3

You can try the following instead of CASE

declare @var1 int, @var2 int, @var3 int

set @var1 = 1
set @var2 = 2
Set @var3 = 3

IF 1 IN (@var1, @var2, @var3) or 2 in (@var1, @var2, @var3) or 3 in (@var1, @var2, @var3)
BEGIN
    Select 1;
END

else 
BEGIN
    select 0;
END

Answer 4

If you don't need to keep the variables, then you can write a loop such as,

SET @found = 0
WHILE @var1 IS NOT NULL AND @found = 0
BEGIN
  IF @var1 IN (1, 2, 3) 
  BEGIN SET @found = 1  END
  ELSE
  BEGIN
    SET @var1 = @var2; SET @var2 = @var3; SET @var3 = NULL
  END
END

...but that isn't tabley. We are DBAs and we use tables.