title: Is running several single 'select where id =...' worse than running a single 'select where id in (...)'?
body: <p>Say I have two tables
  : </p>
  
  <pre><code>ID      ATTRIBUTE
  ------ -----------
  1        'FOO'
  2        'BAR'
  4        'BIZZ'
  
  ID      ATTRIBUTE2
  ------ -----------
  1        'FOO2'
  2        'BAR2'
  3        'BIZZ2'
  </code></pre>
  
  <p>Now I want to fetch all these attributes. </p>
  
  <p>The way I see it, I have two ways of doing it, I can either query both tables, one ID at a time, or I can query both tables for all the IDs and go through the resulting set to extract the attributes. </p>
  
  <p>Option 1:</p>
  
  <pre><code>List&lt;MyObj&gt; myObjs = new ArrayList&lt;MyObj&gt;();
  for (int i = 1; i&lt;5; i++)
  {
      String sql1 = "select  attribute from table1 where id = ?";
      String sql2 = "select attribute2 from table2 where id = ?";
      PreparedStatement ps = conn.prepareStatement(sql);
      ps.setInt(1, i); 
      PreparedStatement ps2 = conn.prepareStatement(sql2);
      ps2.setInt(1,i);  
  
      ResultSet rs1 = ps.executeQuery();
      ResultSet rs2 = ps2.executeQuery();
  
      myObjs.add(new MyObj(i, rs1.getString("attribute1"), rs2.getString("attribute2")); 
      //No null handling but.
  
  }
  </code></pre>
  
  <p>Option 2:</p>
  
  <pre><code>    String sql1 = "select id, attribute from table1 where id in (?)"; 
      String sql2 = "select id, attribute2 from table2 where id in (?)";
      PreparedStatement ps = conn.prepareStatement(sql);
      ps.setArray(1, new ArrayList&lt;Integer&gt;(1,2,3,4,5); 
      PreparedStatement ps2 = conn.prepareStatement(sql2);
      ps2.setArray(1, new ArrayList&lt;Integer&gt;(1,2,3,4,5);
  
      ResultSet rs1 = ps.executeQuery();
      ResultSet rs2 = ps2.executeQuery();
  
      Hashmap&lt;Integer,MyObj&gt; myObjs = new HashMap&lt;Integer, MyObj&gt;();
      while (rs.next())
            {
            myOjbs.add(rs.getInt("id")), new MyObj(rs.getInt("id"), rs.getString("attribute")));
            }
  
  
      while (rs2.next())
           {
                myObjs.get(rs2.getInt("id")).setAttribute2(rs2.getString("attribute2"))
           }
  </code></pre>
  
  <p>Option 1 seems a lot simpler for handling scenarios where one of the attributes might be missing. However, there are repeated SQL calls which might repeat millions of times.  Option 2 has less SQL calls, but requires handling scenarios where the lists might be out of sync. </p>
  
  <p>The question is - is one of these solutions obviously wrong? </p>