SQL 查询:如何将 IN() 转换为 JOIN?
我有很多这样的 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.
相关文章