CODEWITHSUNDEEP
sql-serverselect
FDavidov
FDavidov

Reputation: 3675

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

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:

  1. Is this doable in SQL Server?

  2. If the answer is YES, how should the SELECT be phrased?

Thanks in advance.

Upvotes: 0

Views: 79

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

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)

Upvotes: 2

Related Questions