SQL WHERE.. IN clause multiple columns

Question

i need to implement the following query in SQL Server

select *
from table1
WHERE  (CM_PLAN_ID,Individual_ID)
IN
(
 Select CM_PLAN_ID, Individual_ID
 From CRM_VCM_CURRENT_LEAD_STATUS
 Where Lead_Key = :_Lead_Key
)

but the WHERE..IN clause allows only 1 column. How to compare 2 or more columns with another inner select?

Answer 1

You'll want to use the WHERE EXISTS syntax instead.

SELECT *
FROM table1
WHERE EXISTS (SELECT *
              FROM table2
              WHERE Lead_Key = @Lead_Key
                        AND table1.CM_PLAN_ID = table2.CM_PLAN_ID
                        AND table1.Individual_ID = table2.Individual_ID)

Answer 2

You can make a derived table from the subquery, and join table1 to this derived table:

select * from table1 INNER JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual

This only works if table2 does not contain duplicate pairs of CM_PLAN_ID, Individual_ID (such as if it's the PK). Otherwise you'll need to use DISTINCT.

Answer 3

A simple EXISTS clause is cleanest

select *
from table1 t1
WHERE
EXISTS
(
 Select * --or 1. No difference.
 From CRM_VCM_CURRENT_LEAD_STATUS Ex
 Where Lead_Key = :_Lead_Key
-- correlation here
AND
t1.CM_PLAN_ID = Ex.CM_PLAN_ID AND t1.CM_PLAN_ID =  Ex.Individual_ID
)

If you have multiple rows in the correlation then a JOIN gives multiple rows in the output, so you'd need distinct. Which usually makes the EXISTS more efficient.

Note "SELECT" * with a JOIN would also include columns from the row limiting tables

Answer 4

Why use WHERE EXISTS or DERIVED TABLES when you can just do a normal inner join:

SELECT t.*
FROM table1 t
INNER JOIN CRM_VCM_CURRENT_LEAD_STATUS s
    ON t.CM_PLAN_ID = s.CM_PLAN_ID
    AND t.Individual_ID = s.Individual_ID
WHERE s.Lead_Key = :_Lead_Key

If the pair of (CM_PLAN_ID, Individual_ID) isn't unique in the status table, you might need a SELECT DISTINCT t.* instead.

Answer 5

I founded easier this way

Select *

from table1

WHERE (convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID))

IN

( Select convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)

From CRM_VCM_CURRENT_LEAD_STATUS

Where Lead_Key = :_Lead_Key )

Hope this help :)

Answer 6

I liked your answer Lisandro! Even though in the end, the string concatonation did not meet my needs, I see where it will be an important technique for "quick and dirty" queries. I also do not think you would want to use it where high performance is required.