title: Performance: LEFT JOIN vs SUBQUERY
body: <p>I'm using PostgreSQL 9.3 and have the following tables (simplified to only show the relevant fields):</p>
  
  <pre><code>SITES:
  id
  name
  ...
  
  DEVICES:
  id
  site_id
  mac_address UNIQUE
  ...
  </code></pre>
  
  <p>Given the <code>mac_address</code> of a particular device, and I want to get the details of the associated <code>site</code>. I have the following two queries:</p>
  
  <p>Using LEFT JOIN:</p>
  
  <pre><code>SELECT s.* FROM sites s
  LEFT JOIN devices d ON s.id = d.site_id
  WHERE d.mac_address = '00:00:00:00:00:00';
  </code></pre>
  
  <p>Using SUBQUERY:</p>
  
  <pre><code>SELECT s.* FROM sites s
  WHERE s.id IN (SELECT d.site_id FROM devices d WHERE d.mac_address = '00:00:00:00:00:00');
  </code></pre>
  
  <p>Which of the two queries would have the best performance over an infinitely growing database? I have always leaned towards the <code>LEFT JOIN</code> option, but would be interested to know how the performance of both rates on a large data set.</p>