"select ... where in"和联接的平等

Question

假设我有一个这样的table1:

id | itemcode
-------------
1  | c1
2  | c2
...

和这样的table2:

item | name
-----------
c1   | acme
c2   | foo
...

下面两个查询在每个条件下返回相同的结果集吗?

SELECT id, itemcode 
FROM table1 
WHERE itemcode IN (SELECT DISTINCT item 
                  FROM table2 
                  WHERE name [some arbitrary test])
SELECT id, itemcode 
FROM table1 
   JOIN (SELECT DISTINCT item 
          FROM table2
          WHERE name [some arbitrary test]) items 
         ON table1.itemcode = items.item

除非我真的错过了一些愚蠢的事情，否则我会说是的。但我已经做了两个查询归结为这种形式，我得到了不同的结果。有一些嵌套查询使用WHERE IN，但是对于最后一步，我注意到JOIN要快得多。嵌套查询都是完全隔离的，所以我不相信它们是问题所在，所以我只是想消除我对上面的误解的可能性。

谢谢你的真知灼见。

编辑

两个原始查询:

SELECT imitm, imlitm, imglpt 
    FROM jdedata.F4101 
    WHERE imitm IN 
  (SELECT DISTINCT ivitm AS itemno 
       FROM jdedata.F4104 
       WHERE ivcitm IN 
    (SELECT DISTINCT ivcitm AS legacycode 
             FROM jdedata.F4104 
              WHERE ivitm IN 
      (SELECT DISTINCT tritm 
               FROM trigdata.F4101_TRIG)
    )
  )

SELECT orig.imitm, orig.imlitm, orig.imglpt 
    FROM jdedata.F4101 orig 
        JOIN 
        (SELECT DISTINCT ivitm AS itemno 
        FROM jdedata.F4104 
        WHERE ivcitm IN 
              (SELECT DISTINCT ivcitm AS legacycode 
              FROM jdedata.F4104
               WHERE ivitm IN 
                 (SELECT DISTINCT tritm 
                  FROM trigdata.F4101_TRIG))) itemns 
ON orig.imitm = itemns.itemno

EDIT 2

虽然我仍然不明白为什么查询返回不同的结果，似乎我们的逻辑从一开始就有缺陷，因为我们在某些部分使用了错误的列。请注意，我并不是说我在解释上面写的查询时犯了错误，或者有一些打字错误，我们只是需要选择一些不同的东西。

通常情况下，我不会休息，直到我把这些事情弄清楚，但我很累，这是我自1月份以来第一次有超过一天的假期，所以我现在真的不能再费心去搜索了。我相信这里给出的建议以后会派上用场的。对于所有的帮助，我都投了赞，我接受了supercube的答案，主要是因为他的评论让我走得最远。不过还是谢谢大家!如果我后来发现了更多的信息，我会试着记得ping回来。

Answer 1

由于table2.item不可为空，因此两个版本是等效的。您可以从IN版本中删除distinct，这是不需要的。你可以查看这三个版本和它们的执行计划:

SELECT id, itemcode FROM table1 WHERE itemcode IN
  ( SELECT item FROM table2 WHERE name [some arbitrary test] )
SELECT id, itemcode FROM table1 JOIN
  ( SELECT DISTINCT item FROM table2 WHERE name [some arbitrary test] )
  items ON table1.itemcode = items.item
SELECT id, itemcode FROM table1 WHERE EXISTS
  ( SELECT * FROM table2 WHERE table1.itemcode = table2.item 
                           AND (name [some arbitrary test]) )

Answer 2

理想情况下，我希望看到结果集之间的差异。
-你有重复记录吗?
-一个集合是否总是另一个集合的子集
与另一组相比，一组是否同时有"额外"one_answers"缺失"的记录?

也就是说，逻辑应该是等价的。我最好的猜测是这里有一些空字符串项;因为Oracle版本的NULL CHAR/VARCHAR只是一个空字符串。如果你没有做好准备，这可能会产生非常奇怪的结果。

Answer 3

两个查询执行一个半连接，即没有table2的属性出现在最上面的SELECT(结果集)中。

在我看来，您的第一个查询最容易识别为半连接，EXISTS更是如此。另一方面，优化器无疑会有不同的看法;)

Answer 4

您也可以尝试直接连接到第二个表

SELECT DISTINCT id, itemcode 
FROM table1 
INNER JOIN table2 ON table1.itemcode = table2.item   
WHERE name [some arbitrary test] )

不需要distinct如果item是主键或唯一的

Exists和Inner Join应该有相同的执行速度，而IN的执行速度比较慢。

Answer 5

我会在那里寻找一些数据类型转换。

create table t_vc (val varchar2(6));
create table t_c (val char(6));
insert into t_vc values ('12345');
insert into t_vc values ('12345 ');
insert into t_c values ('12345');
insert into t_c values ('12345');
select t_c.val||':'
from t_c
where val in (select distinct val from t_vc);
select c.val||':'
from t_vc v join (select distinct val from t_c) c on v.val=c.val;