取而代之的是使用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)

Postgres SQL  : version 9.6
Total records on tables : mjr_agent = 145, mjr_transaction_item = 91800

1.使用EXISTS[平均查询时间: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.使用两行IN子句[平均查询时间: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.使用inner JOIN模式[平均查询时间: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

所以，我选择了第二种选择。

如果你想要一个表，然后使用以下查询

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

并表中数据如下

id|name|adde|city
1   a   ad  ca
2   b   bd  bd
3   a   ad  ad
4   b   bd  bd
5   c   cd  cd

然后输出如下

id|name|adde|city
1   a   ad  ca
2   b   bd  bd

我们可以简单地这么做。

   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

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)

取而代之的是使用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)