title: How to avoid unnecessary selects and joins in HQL and Criteria
body: <p>I have been trying different combinations of <strong>HQL</strong> and <strong>Criteria</strong> and I haven't been able to avoid some unnecessary <strong>joins</strong> (in both) and some unnecessary <strong>selects</strong> (in Criteria).</p>
  
  <p>In our scenario, we have a <strong>@ManyToMany</strong> relationship between <strong>Segment</strong> and <strong>Application</strong> entities (navigation is from Segment to Applications).</p>
  
  <p>First I tried this <strong>Criteria</strong>:</p>
  
  <pre><code>Application app = ...
  List&lt;Segment&gt; segments = session.createCriteria(Segment.class)
      .createCriteria(Segment.APPLICATIONS)
      .add(Restrictions.idEq(app.getId()))
      .list();
  </code></pre>
  
  <p>Wich produces this SQL:</p>
  
  <pre><code>select
      this_.id as id1_1_,
      this_.description as descript2_1_1_,
      this_.name as name1_1_,
      applicatio3_.segment_id as segment1_1_,
      applicatio1_.id as app2_,               &lt;==== unnecessary APPLICATIONS columns
      applicatio1_.id as id7_0_,
      applicatio1_.name as name7_0_,
      applicatio1_.accountId as accountId7_0_,
      applicatio1_.applicationFlags as applicat5_7_0_,
      applicatio1_.description_ as descript6_7_0_,
  from
      SEGMENTS this_ 
  inner join
      SEGMENTS_APPLICATIONS applicatio3_ 
          on this_.id=applicatio3_.segment_id 
  inner join                                       &lt;==== unnecessary join
      APPLICATIONS applicatio1_ 
          on applicatio3_.app_id=applicatio1_.id 
  where
      applicatio1_.id = ?
  </code></pre>
  
  <p>As you can see, <em>Criteria selects columns from APPLICATIONS</em>, which I don't want to be selected. I haven't found a way to do it (is it possible?). Also, it <em>joins with APPLICATIONS</em>, which I think is not necessary because the application id is already in the join table SEGMENTS_APPLICATIONS (the same happens with HQL).</p>
  
  <p>(As an additional doubt, I'd like to know a Restriction that uses the app directly, and not app.getId(). As you will see, I could do that in the HQL version of the query)</p>
  
  <p>Since I couldn't limit the select part (I don't need Application properties) I tried this <strong>HQL</strong> with the "select" clause:</p>
  
  <pre><code>Application app = ...
  List&lt;Segment&gt; segments = session.createQuery(
      "select s from Segment s join s.applications as app where app = :app")
      .setParameter("app", app)
      .list();
  </code></pre>
  
  <p>wich produces:</p>
  
  <pre><code>select
      segment0_.id as id1_,
      segment0_.description as descript2_1_,
      segment0_.name as name1_,
  from
      SEGMENTS segment0_ 
  inner join
      SEGMENTS_APPLICATIONS applicatio1_ 
          on segment0_.id=applicatio1_.segment_id 
  inner join                                        &lt;==== unnecessary join
      APPLICATIONS applicatio2_ 
          on applicatio1_.app_id=applicatio2_.id 
  where
      applicatio2_.id=? 
  </code></pre>
  
  <p>You can see the <em>HQL doesn't select properties from Application</em> (thanks to the "select s" part), but <em>still joins the APPLICATIONS</em> table, which I think is unnecessary. How can we avoid that?</p>
  
  <p>(As a side note, notice that in HQL I could use app directly, and not app.getId() like in the Criteria)</p>
  
  <p>Can you please help me find a way to avoid "selects" in Criteria and unnecessary "joins" in both Criteria and HQL?</p>
  
  <p>(This example is with @ManyToMany but I think it also happens with @OneToMany and also with @ManyToOne and @OneToOne, even with fetch = LAZY).</p>
  
  <p>Thank you very much,
  Ferran</p>