How to rewrite a subquery to use joins when you used DISTINCT in the subquery?

Question

I have a query where I use a sub query, and I would like to rewrite into a join, to have better performance.

The subquery uses DISTINCT as there are many records, and the problem I have is that they end up multiple times in the join when I use the join.

So how do I rewrite a query like this to use join:

SELECT * 
FROM   table1 a 
       JOIN table2 b 
         ON b.field1 = a.field1 
WHERE  b.field3 = 1531 
       AND a.field4 = 0 
       AND a.field5 IN (SELECT DISTINCT field5 
                        FROM   table3 
                        WHERE  field6 = 172) 

Answer 1

Something like:

SELECT * 
FROM   table1 a 
       JOIN table2 b ON b.field1 = a.field1 
       JOIN ( SELECT DISTINCT field5 
                        FROM   table3 
                        WHERE  field6 = 172 ) C 
       ON C.field5 = a.field5

WHERE  b.field3 = 1531 AND a.field4 = 0 

It's hard to say without seeing the actual data, so if this doesn't work, please let me know.

Answer 2

Relocate the DISTINCT keyword to the SELECT clause in the main query e.g.

SELECT DISTINCT a.*, b.*
FROM   table1 a 
       JOIN table2 b 
         ON b.field1 = a.field1 
       JOIN table3 c
         ON a.field5 = c.field5 
WHERE  b.field3 = 1531 
       AND a.field4 = 0 
       AND c.field6 = 172;

You should review the use of SELECT * in your query i.e. not best practise.