title: Left join on unrelated tables in Query DSL and JPA
body: <p>I have two unrelated tables, each one with field email. I need a query which introduces column taken from second table if emails match or will be null if no match is found. In SQL this is easy:</p>
  
  <pre><code>SELECT tableA.id, tableA.email, tableB.name
   FROM tableA
   LEFT JOIN tableB ON tableA.email=tableB.email
   ORDER BY tableB.name
  </code></pre>
  
  <p>Unfortunately JPA doesn't allow joins over unrelated entities so I converted it to:</p>
  
  <pre><code>SELECT tableA.id, tableA.email,
         (SELECT tableB.name FROM tableB WHERE tableB.email=tableA.email) AS aname
    FROM tableA
    ORDER BY aname 
  </code></pre>
  
  <p>Now, it works as JPA query but we are using Query DSL so off we go to converting it:</p>
  
  <pre><code>JPQLQuery query = new JPAQuery(em);
  List&lt;Dto&gt; items=query.from(qTableA)
    .list(new QDto(qTableA.id, qTableA.email,
                   new JPASubQuery().from(qTableB)
                    .where(qTableB.email.eq(qTableA.email)).unique(qTableB.name)))
  </code></pre>
  
  <p>It works but now I have no idea how to implement sorting and filtering by field introduced by subquery.</p>
  
  <p><code>Dto</code> is a POJO used to collect results, <code>QDto</code> is a class autogenerated from <code>Dto</code>.</p>
  
  <p>Question is: how to join two unrelated tables using Query DSL and JPA and avoiding native SQL? Is it possible? Sorting and filtering on fields from tableA and tableB.name is a requirement.</p>