Operators similar to 'in' that compares two sets

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!

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.

StackExchange Database Administrators Q#176480, answer score: 10