SQL WHERE .. IN 절 여러 열

SQL WHERE .. 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
)

그러나 WHERE..IN 절은 하나의 열만 허용합니다. 두 개 이상의 열을 다른 내부 SELECT와 어떻게 비교할 수 있습니까?

---

하위 쿼리에서 파생 테이블을 만들고 table1을이 파생 테이블에 조인 할 수 있습니다.

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

---

대신 WHERE EXISTS 구문을 사용하고 싶을 것입니다.

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)

---

간단한 EXISTS 절이 가장 깨끗합니다.

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
)

상관 관계에 여러 행이있는 경우 JOIN은 출력에 여러 행을 제공하므로 고유해야합니다. 일반적으로 EXISTS가 더 효율적입니다.

JOIN이있는 "SELECT"*는 행 제한 테이블의 열도 포함합니다.

---

select * from tab1 where (col1,col2) in (select col1,col2 from tab2)

참고 :
Oracle은 선택된 하나 이상의 열이 NULL 인 행을 무시합니다. 이 경우 NVL -Funktion을 사용하여 NULL을 특수 값 (값에 포함되지 않아야 함)에 맵핑하려고합니다.

select * from tab1
where (col1, NVL(col2, '---') in (select col1, NVL(col2, '---') from tab2)

---

솔루션에 대한 경고 :

로크가 고유하지 않으면 많은 기존 솔루션이 잘못된 출력을 제공합니다.

테이블을 생성하는 유일한 사람이라면 관련이 없을 수 있지만 테이블 중 하나에 고유 한 행이 포함되어 있지 않을 때 여러 솔루션에서 해당 코드와 다른 수의 출력 행을 제공합니다.

문제 진술에 대한 경고 :

여러 개의 열이 포함되어 있으면 원하는 것을 신중하게 생각하십시오.

두 개의 열이있는 in을 보면 두 가지를 의미한다고 상상할 수 있습니다.

1. 열 a와 열 b의 값은 다른 테이블에 독립적으로 나타납니다.
2. 열 a와 열 b의 값이 같은 행에 다른 테이블에 함께 나타납니다.

시나리오 1은 매우 사소합니다. 간단히 두 개의 IN 문을 사용하십시오.

대부분의 기존 답변에 따라 시나리오 2 (및 간단한 판단)에 대해 언급되고 추가 된 접근 방식에 대한 개요를 제공합니다.

존재 (안전, SQL Server에 권장)

@mrdenny가 제공 한 EXISTS는 당신이 찾고있는 것과 똑같이 들립니다. 그의 예는 다음과 같습니다.

SELECT * FROM T1
WHERE EXISTS
(SELECT * FROM T2 
 WHERE T1.a=T2.a and T1.b=T2.b)

LEFT SEMI JOIN (안전, 지원하는 방언에 권장)

이것은 매우 간결한 방법이지만, 불행히도 SQL 서버를 포함한 대부분의 SQL 방언은이를 지원하지 않습니다.

SELECT * FROM T1
LEFT SEMI JOIN T2 ON T1.a=T2.a and T1.b=T2.b

여러 IN 문 (안전하지만 코드 중복에주의)

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:

1. Joining directly on T2, without a safe subquery. Resulting in the risk of duplication)
2. SELECT * (Guaranateed to get columns from T2)
3. SELECT c (Does not guarantee that your column comes and always will come from T1)
4. 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.

---

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.

---

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.

---

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

---

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

---

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

---

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 <boolean expression>
   and (<boolean expression> OR (ColA, ColB) in (select A, B ...))
   and <boolean expression>
   ...

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

---

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 :)

---

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.

참고URL : https://stackoverflow.com/questions/1136380/sql-where-in-clause-multiple-columns