SQL query: how to translate IN () to JOIN?

Question

I have many SQL queries, such as:

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

These queries must be run on different database systems (MySql, Oracle, DB2, MS-Sql, Hypersonic), so I can only use regular SQL syntax.

Here I read that with MySql the statement is INnot optimized and it is very slow, so I want to switch this to JOIN.

I tried:

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

But this does not account for the keyword DISTINCT.

Question: How to get rid of duplicate lines using an approach JOIN?

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, , .

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, .

Answer 3

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

Although, if you need to support several different ends of the database, you probably want to provide everyone with their own set of repository classes in their data layer so that you can optimize your queries for each. It also gives you the opportunity to stay in other types of databases, or xml, or web services, or something else that should come in the future.

Answer 4

I am not sure to understand what your problem is. Why don't you try this:

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