title: Query with JOIN or WHERE (SELECT COUNT(*) ...) >= 1?
body: <p>I have a database schema which contains about 20 tables. For the sake of my question, I simplify asking with only 3 tables :</p>
  
  <pre><code>* posts
      id
      title
      ...
  * posts_users
      post_id
      user_id
      status (draft, published, etc)
      ...
  * users
      id
      username
      ... 
  </code></pre>
  
  <p>For reasons which are out of this topic, <code>Posts</code> and <code>Users</code> have a "many to many" relationship and the status field is part of <code>posts_users</code> (could have been in the <code>posts</code> table).</p>
  
  <p><strong>I'd like to get published posts. I hesitate between 2 kinds of query:</strong></p>
  
  <pre><code>SELECT posts.*
  FROM posts
  INNER JOIN posts_users ON posts_users.post_id = posts.id
  WHERE status = 'published'
  </code></pre>
  
  <p><strong>or</strong></p>
  
  <pre><code>SELECT posts.*
  FROM posts
  WHERE (
      SELECT COUNT(*)
      FROM posts_users
      WHERE post_id = posts.id
          AND status = 'published'
  ) &gt;= 1
  </code></pre>
  
  <p>(I have simplified my question, but in real, posts are linked to far more other data to filter)</p>
  
  <p>My DB is SQLite. My questions are:</p>
  
  <ol>
  <li>What is the difference?</li>
  <li>Which way of querying is best in terms of performance?</li>
  </ol>