title: MySQL query - derived table vs left join where
body: <p>Given <code>posts</code> and <code>posts_tags</code>:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE posts (
      id SERIAL PRIMARY KEY,
      external_id varchar(10) UNIQUE,
      title varchar(255),
      body TEXT
  )
  
  CREATE TABLE posts_tags (
      id SERIAL PRIMARY KEY,
      post_external_id varchar(10) REFERENCES posts(external_id),
      tag varchar(255),
      UNIQUE KEY (post_external_id, tag)
  )
  </code></pre>
  
  <p>As you can see the above tables store posts and their tags for a backend serving REST APIs.</p>
  
  <p>There is a <code>get-post-by-external-id</code> API that will return the post with its tags identified by the post's <code>external_id</code>. Given this use case, I'm wondering which query below executes faster and consumes less resources?</p>
  
  <h1>Derived table</h1>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT posts.*, posts_tags.tag 
  FROM (SELECT * FROM posts WHERE external_id = ?) AS posts 
  LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id
  </code></pre>
  
  <h1>Left join where</h1>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT posts.*, posts_tags.tag 
  FROM posts 
  LEFT JOIN posts_tags ON posts.external_id = posts_tags.post_external_id
  WHERE posts.external_id = ?
  </code></pre>
  
  <p>I'm not sure, but I guess the <code>WHERE</code> filters the intermediate results after the <code>LETF JOIN</code> happens?</p>