# 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!

• Do you want to compare two sets of values (that means two sets of rows in a table) or two sets of variables (a limited number of them) and see what values are the same? Your code implies 3 variables and the second set is 1,2,3, right? Can you give examples of the two sets? Please give a positive and a negative example. Jun 17, 2017 at 14:49
• @Grimaldi, thanks for your reminder and I've updated the question. Please take a look and see whether it's clear now. Jun 17, 2017 at 23:42
• The question now is of comparing 3 columns to 3 runtime values and reporting any one matching column. This would be "simplified" by converting the table to have 1 column of the values, which you could do with "SELECT key, var1 FROM #tab1 UNION ALL SELECT key, var2 ... UNION ALL SELECT key, var3", but I don't think that is better than your code or will run quicker. You could put it in a view. Your formula outputs 1 on each row which contains 1 or more of the runtime inputs, which I presume is what you want to get, or else, you want to select rows with a=1 in the example, as a result set? Jun 19, 2017 at 9:59
• Questions about your revised example: do you really need to return all rows (with a truth/false value), or do you really just care about returning the rows where any of your 3 columns match your other 3 values? Are there other relevant columns you need to return, like an ID of some sort? Jun 19, 2017 at 13:50
• @BradC, thanks for your help! I do need all rows to be returned. There are other rows I wish to return along with the true/false rows. Does it change the questions? Jun 24, 2017 at 22:27

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)
``````
• Thanks for your help! When I posted this question, I thought there would be a universal function to solve such type of question. But it turned out to be no such solution apparently.... Jun 24, 2017 at 22:31

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.

• Sorry my question wasn't clear in the first place. The variables I referred to was actually columns in data base. So your answer cannot solve my question directly. Can you take a look at the updated question? Thanks! Jun 17, 2017 at 23:45
• The answer works fine with variables or columns. Did you try it? Jun 18, 2017 at 19:06

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
``````
• Sorry my question wasn't clear in the first place. The variables I referred to was actually columns in data base. Can you take a look at the updated question? Thanks! Jun 17, 2017 at 23:46

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.

• Thanks for your help and I've updated the question. Can you take a look at it? Jun 17, 2017 at 23:43