SQL Server - WHERE <several columns> in (<list of columns values>)

Question

I have done this long ago in other DBMSs (Oracle or MySQL... don't really remember) and I'm looking for the way to do this in SQL Server, if possible at all.

Suppose you have a table with several columns, say A, B, C, ... M. I wish to phrase a select from this table where columns A, B, and C display specific sets of value or, in other words, a list of values combinations.

For instance, I wish to retrieve all the records that match any of the following combinations:

  A         B           C
  1         'Apples'    '2016-04-12'
 56         'Cars'      '2014-02-11'
 ....

Since the list of possible combinations may be quite long (including the option of an inner SELECT), it would not be practical to use something like:

WHERE ( A = 1    AND B = 'Apples'   and C = '2016-04-12' ) OR
      ( A = 56   AND B = 'Cars'     and C = '2014-02-11' ) OR
      ...

As stated, I did use this type of construct in the past and it was something like:

SELECT *
  FROM MyTable
 WHERE (A,B,C) IN (SELECT A,B,C FROM MYOtherTable) ;

[Most likely this syntax is wrong but it shows what I'm looking for]

Also, I would rather avoid Dynamic SQL usage.

So, the questions would be:

Is this doable in SQL Server?
If the answer is YES, how should the SELECT be phrased?

Thanks in advance.

Pரதீப் · Accepted Answer · 2017-01-10 16:32:31Z

2

You can use JOIN

SELECT m1.*
FROM   MyTable m1
       JOIN MYOtherTable m2
         ON m1.A = m2.A
            AND m1.B = m2.B
            AND m1.C = m2.C

or Exists

SELECT m1.*
FROM   MyTable m1
WHERE  EXISTS (SELECT 1
               FROM   MYOtherTable m2
               WHERE  m1.A = m2.A
                      AND m1.B = m2.B
                      AND m1.C = m2.C)

edited Jan 10, 2017 at 16:32

answered Jan 10, 2017 at 16:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

And to answer op's question: no, it can't be done directly in SQL Server

FDavidov Over a year ago

We, thank you both @Lamak and Prdp for your response. The JOIN option would work for the (misleading) example I gave (my apologies), but it would not work for the intended question (as Lamak noted). This, however, gives me an idea.... make a temporary table with the list of possible combinations. Again, thank you guys.

Collectives™ on Stack Overflow

SQL Server - WHERE <several columns> in (<list of columns values>)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related