SQL查询：如何将IN（）转换为JOIN？

Question

我有很多像这样的SQL查询：

SELECT o.Id, o.attrib1, o.attrib2 
  FROM table1 o 
WHERE o.Id IN (
                SELECT DISTINCT Id 
                  FROM table1
                     , table2
                     , table3 
                 WHERE ...
               )

这些查询需要在不同的数据库引擎(MySql、Oracle、DB2、MS-Sql、Hypersonic)上运行，因此我只能使用通用的 SQL 语法。

在这里我看到，使用 MySql 的 IN 语句没有被优化，速度非常慢，所以我想把它改成 JOIN。 我尝试了:

SELECT o.Id, o.attrib1, o.attrib2 
  FROM table1 o, table2, table3 
  WHERE ...

但这并不考虑 DISTINCT 关键字。

问题：如何使用 JOIN 方法消除重复行？

- tangens

---

当然，你不应该使用那些隐式联接。非常糟糕。如果你忘记了 where 子句，可能会得到一个交叉联接，这将导致你需要使用 distinct。18年前，隐式联接被更好的语法替换了，为什么你还在使用它们呢？ - HLGEM

使用隐式连接肯定是个人口味问题。 - Rob van Laarhoven

Answer 1

要使用JOIN编写此代码，您可以使用内部选择并与其连接：

SELECT o.Id, o.attrib1, o.attrib2 FROM table1 o
JOIN (
  SELECT DISTINCT Id FROM table1, table2, table3 WHERE ...
) T1
ON o.id = T1.Id

我不确定这样做会更快，但也许...你可以自己尝试一下。

总的来说，仅限于适用于多个数据库的SQL语句并不会带来最佳性能。

- Mark Byers

---

是的，在MySql 5上，这个速度要快得多。谢谢！ - tangens

但是在Oracle上，我遇到了一个问题，无法将关键字“FOR UPDATE”附加到此查询。请参见http://stackoverflow.com/questions/2586014/how-can-i-use-for-update-with-a-join-on-oracle - tangens

Answer 2

但是这个查询语句中没有考虑到DISTINCT关键字。在子查询中不需要使用DISTINCT，因为IN关键字将在外部查询中返回一行，无论它在子查询中匹配了一行还是一百行。因此，如果您想提高查询的性能，去掉DISTINCT是一个很好的开始。

调整in子句的一种方法是改用exists来重写它们。根据数据的分布情况，这可能更有效，或者可能更慢。通过调整，基准测试是至关重要的。

SELECT o.Id, o.attrib1, o.attrib2 
FROM table1 o 
WHERE EXISTS (
  SELECT  Id FROM table1 t1, table2 t2, table3 t3 WHERE ... 
  AND  ( t1.id = o.id 
         or t2.id = o.id 
         or t3.id = o.id 
)

不知道你的业务逻辑，那个额外过滤器的精确表述可能是错误的。

顺便提一下，我注意到你在外部查询和子查询中都使用了table1。如果这不是将你实际的SQL转录到此处时出现的错误，你可能需要考虑这是否有意义。最好避免两次查询该表；使用exists可以更容易地避免双重查询。

- APC

Answer 3

SELECT DISTINCT o.Id, o.attrib1, o.attrib2 
  FROM table1 o, table2, table3 
 WHERE ...

如果你需要支持多个不同的数据库后端，你可能想在数据层中为每个数据库后端提供自己的存储库类集合，这样你就可以为每个数据库后端优化查询。这也使你有能力将数据持久化到其他类型的数据库、XML、Web服务或者未来可能出现的任何东西。

- Matthew Vines

Answer 4

我不确定我真正理解你的问题。为什么不试试这个：

SELECT distinct o.Id, o.attrib1, o.attrib2
FROM
table1 o
, table o1
, table o2
...
where
o1.id1 =  o.id
or o2.id = o.id

- ysen