title: SELECT ... WHERE IN vs JOIN
body: <p>Assume we have 4 tables A, B, C, D in <strong>some unspecified relational SQL database</strong>. A references B, also C and D as well. References means that A has column A.refX_id = X.id, X is A, B and C (common foreign key 1:N).</p>
  
  <p>What I want, is to query table A with condition based on columns from all child tables B, C and D. My question is: <strong>What of the following variants is generally better?</strong> (In terms of usability, efficiency, speed.)</p>
  
  <p><em>Variant 1:</em></p>
  
  <pre><code>SELECT DISTINCT A.* FROM A
     JOIN B ON A.refB_id = B.id
     JOIN C ON A.refC_id = C.id
     JOIN D ON A.refD_id = D.id
     WHERE &lt;condition on B&gt; AND &lt;condition on C&gt; AND &lt;condition on D&gt;;
  </code></pre>
  
  <p>Which I like more from perspective of a database, but looks a little bit harder to program.</p>
  
  <p><em>Variant 2:</em></p>
  
  <pre><code>SELECT id FROM B WHERE &lt;condition on B&gt;; # result store to array "BIds" on program side
  SELECT id FROM C WHERE &lt;condition on C&gt;; # result store to array "CIds" on program side
  SELECT id FROM D WHERE &lt;condition on D&gt;; # result store to array "DIds" on program side
  
  SELECT A.* FROM A
     WHERE refB_id IN (&lt;B_ids&gt;) AND refC_id IN (&lt;C_ids&gt;) AND refD_id IN (&lt;D_ids&gt;);
  
  # &lt;B_ids&gt; menas expand whole array of ids, which can result in a very long query string
  </code></pre>
  
  <p>I thought that <em>Variant 2</em> is completelly smut and unusable with potentially large data. But I've heard, that a lot of frameworks normally use it, because it is relativelly straightforward. Is it sort of legal way to query data like this in general case, if I know that the content of the "IN" clause is taken from a result of another query(ies)?</p>