SQL query: how to translate IN() into a JOIN?
- by tangens
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 ...
)
These queries have to run on different database engines (MySql, Oracle, DB2, MS-Sql, Hypersonic), so I can only use common SQL syntax.
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.
I tried:
SELECT o.Id, o.attrib1, o.attrib2 FROM table1 o, table2, table3 WHERE ...
But this does not take into account the DISTINCT keyword.
Question: How do I get rid of the duplicate rows using the JOIN approach?
