SQL 查询:如何将 IN() 转换为 JOIN?

2022-01-14 00:00:00 performance sql db2 mysql oracle

我有很多这样的 SQL 查询:

I have a lot of SQL queries like this:

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 语法.

These queries have to run on different database engines (MySql, Oracle, DB2, MS-Sql, Hypersonic), so I can only use common SQL syntax.

这里 我读到了，用 MySqlIN 语句没有优化，而且速度很慢，所以我想将其切换为 JOIN.

Here I read, that with MySql the IN statement isn't optimized and it's really slow, so I want to switch this into a JOIN.

我试过了:

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

但这并没有考虑到 DISTINCT 关键字.

But this does not take into account the DISTINCT keyword.

问题:如何使用 JOIN 方法去除重复的行?

Question: How do I get rid of the duplicate rows using the JOIN approach?

推荐答案

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

To write this with a JOIN you can use an inner select and join with that:

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

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

I'm not sure this will be much faster, but maybe... you can try it for yourself.

一般而言，将自己限制在可在多个数据库上运行的 SQL 不会带来最佳性能.

In general restricting yourself only to SQL that will work on multiple databases is not going to result in the best performance.

相关文章