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 can I compare 2 or more columns with another inner SELECT

User · Answer

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.

User · Answer

WARNING ABOUT SOLUTIONS   MANY EXISTING SOLUTIONS WILL GIVE THE WRONG OUTPUT IF ROWS ARE NOT UNIQUE  If you are the only person creating tables  this may not be relevant  but several solutions will give a different number of output rows from the code in question  when one of the tables may not contain unique rows   WARNING ABOUT PROBLEM STATEMENT   IN WITH MULTIPLE COLUMNS DOES NOT EXIST  THINK CAREFULLY WHAT YOU WANT  When I see an in with two columns  I can imagine it to mean two things    The value of column a and column b appear in the other table independently The values of column a and column b appear in the other table together on the same row   Scenario 1 is fairly trivial  simply use two IN statements    In line with most existing answers  I hereby provide an overview of mentioned and additional approaches for Scenario 2  and a brief judgement    EXISTS  Safe  recommended for SQL Server   As provided by  mrdenny  EXISTS sounds exactly as what you are looking for  here is his example   SELECT   FROM T1 WHERE EXISTS  SELECT   FROM T2   WHERE T1 a T2 a and T1 b T2 b    LEFT SEMI JOIN  Safe  recommended for dialects that support it   This is a very concise way to join  but unfortunately most SQL dialects  including SQL server do not currently suppport it   SELECT   FROM T1 LEFT SEMI JOIN T2 ON T1 a T2 a and T1 b T2 b   Multiple IN statements  Safe  but beware of code duplication   As mentioned by  cataclysm using two IN statements can do the trick as well  perhaps it will even outperform the other solutions  However  what you should be very carefull with is code duplication  If you ever want to select from a different table  or change the where statement  it is an increased risk that you create inconsistencies in your logic   Basic solution  SELECT   from T1 WHERE a IN  SELECT a FROM T2 WHERE something  AND b IN  SELECT b FROM T2 WHERE something    Solution without code duplication  I believe this does not work in regular SQL Server queries   WITH mytmp AS  SELECT a  b FROM T2 WHERE something   SELECT   from T1  WHERE a IN  SELECT a FROM mytmp  AND b IN  SELECT b FROM mytmp    INNER JOIN  technically it can be made safe  but often this is not done   The reason why I don t recommend using an inner join as a filter  is because in practice people often let duplicates in the right table cause duplicates in the left table  And then to make matters worse  they sometimes make the end result distinct whilst the left table may actually not need to be unique  or not unique in the columns you select   Futhermore it gives you the chance to actually select a column that does not exists in the left table   SELECT T1   FROM T1 INNER JOIN   SELECT DISTINCT a  b FROM T2  AS T2sub ON T1 a T2sub a AND T1 b T2sub b   Most common mistakes    Joining directly on T2  without a safe subquery  Resulting in the risk of duplication  SELECT    Guaranateed to get columns from T2  SELECT c  Does not guarantee that your column comes and always will come from T1  No DISTINCT or DISTINCT in the wrong place   CONCATENATION OF COLUMNS WITH SEPARATOR  Not very safe  horrible performance   The functional problem is that if you use a separator which might occur in a column  it gets tricky to ensure that the outcome is 100  accurate  The technical problem is that this method often incurs type conversions and completely ignores indexes  resulting in possibly horrible performance  Despite these problems  I have to admit that I sometimes still use it for ad-hoc queries on small datasets   SELECT   FROM T1 WHERE CONCAT a     b  IN   SELECT CONCAT a     b  FROM T2    Note that if your columns are numeric  some SQL dialects will require you to cast them to strings first  I believe SQL server will do this automatically     To wrap things up  As usual there are many ways to do this in SQL  using safe choices will avoid suprises and save you time and headaces in the long run

User · Answer

If you want for one table then use following query  SELECT S    FROM Student info S   INNER JOIN Student info UT     ON S id   UT id     AND S studentName   UT studentName where S id in  1 2  and S studentName in   a   b     and table data as follow  id name adde city 1   a   ad  ca 2   b   bd  bd 3   a   ad  ad 4   b   bd  bd 5   c   cd  cd   Then output as follow  id name adde city 1   a   ad  ca 2   b   bd  bd

User · Answer

You can make a derived table from the subquery  and join table1 to this derived table   select   from table1 LEFT 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 WHERE table2 CM PLAN ID IS NOT NULL

User · Answer

Simple and wrong way would be combine two columns using   or concatenate and make one columns   Select   from XX where col1 col2 in  Select col1 col2 from YY    This would be offcourse pretty slow  Can not be used in programming but if in case you are just querying for verifying something may be used

User · Answer

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

User · Answer

We can simply do this        select      from      table1 t  CRM VCM CURRENT LEAD STATUS c     WHERE  t CM PLAN ID   c CRM VCM CURRENT LEAD STATUS     and t Individual ID   c Individual ID

User · Answer

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

User · Answer

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

User · Answer

Query   select ord num  agent code  ord date  ord amount from orders where  agent code  ord amount  IN  SELECT agent code  MIN ord amount  FROM orders  GROUP BY agent code     above query worked for me in mysql   refer following link --   https   www w3resource com sql subqueries multiplee-row-column-subqueries php

User · Answer

Postgres SQL    version 9 6 Total records on tables   mjr agent   145  mjr transaction item   91800   1 Using with EXISTS  Average Query Time   1 42s   SELECT count txi id   FROM  mjr transaction item txi WHERE  EXISTS   SELECT 1 FROM mjr agent agnt WHERE agnt agent group   0 AND  txi src id   agnt code OR txi dest id   agnt code       2 Using with two lines IN Clause  Average Query Time   0 37s   SELECT count txi id  FROM mjr transaction item txi WHERE  txi src id IN   SELECT agnt code FROM mjr agent agnt WHERE agnt agent group   0    OR txi dest id IN   SELECT agnt code FROM mjr agent agnt WHERE agnt agent group   0     3 Using with INNNER JOIN pattern  Average Query Time   2 9s   SELECT count DISTINCT txi id   FROM mjr transaction item txi INNER JOIN mjr agent agnt ON agnt code   txi src id OR agnt code   txi dest id WHERE  agnt agent group   0   So   I choosed second option

User · Answer

Concatenating the columns together in some form is a  hack   but when the product doesn t support semi-joins for more than one column  sometimes you have no choice   Example of where inner outer join solution would not work   select   from T1   where  lt boolean expression gt     and   lt boolean expression gt  OR  ColA  ColB  in  select A  B          and  lt boolean expression gt           When the queries aren t trivial in nature sometimes you don t have access to the base table set to perform regular inner outer joins   If you do use this  hack   when you combine fields just be sure to add enough of a delimiter in between them to avoid misinterpretations  e g  ColA     -     ColB

User · Answer

select   from tab1 where  col1 col2  in  select col1 col2 from tab2    Note  Oracle ignores rows where one or more of the selected columns is NULL  In these cases you probably want to make use of the NVL-Funktion to map NULL to a special value  that should not be in the values    select   from tab1 where  col1  NVL col2   ---   in  select col1  NVL col2   ---   from tab2

[sql-server] SQL WHERE.. IN clause multiple columns

Examples related to sql-server