title: How to rewrite a subquery to use joins when you used DISTINCT in the subquery?
body: <p>I have a query where I use a sub query, and I would like to rewrite into a join, to have better performance.</p>
  
  <p>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.</p>
  
  <p>So how do I rewrite a query like this to use join:</p>
  
  <pre><code>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) 
  </code></pre>