7

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!

6
  • 1
    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.
    – Grimaldi
    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.
    – Jason
    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?
    – BradC
    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?
    – Jason
    Jun 24, 2017 at 22:27

4 Answers 4

1
+50

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)
1
  • 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....
    – Jason
    Jun 24, 2017 at 22:31
10

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.

2
  • 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!
    – Jason
    Jun 17, 2017 at 23:45
  • 1
    The answer works fine with variables or columns. Did you try it? Jun 18, 2017 at 19:06
4

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
1
  • 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!
    – Jason
    Jun 17, 2017 at 23:46
1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.