title: Question about SQL Server Optmization Sub Query vs. Join
body: <p>Which of these queries is more efficient, and would a modern DBMS (like SQL Server) make the changes under the hood to make them equal?</p>
  
  <pre><code>SELECT DISTINCT S# 
    FROM shipments 
   WHERE P# IN (SELECT P# 
                  FROM parts 
                 WHERE color = ‘Red’)
  </code></pre>
  
  <p>vs.</p>
  
  <pre><code>SELECT DISTINCT S# 
    FROM shipments, parts 
   WHERE shipments.P# = parts.P# 
     AND parts.color = ‘Red’
  </code></pre>