title: JPQL left outer join on many to many relationship
body: <p>I'm trying to write a query (using JPQL) to get all tags and how often each has been referenced by all items. The JPQL is:</p>
  
  <pre><code>SELECT t.id, t.name, SIZE(t.items) FROM Tag t GROUP BY t.id, t.name ORDER BY t.name ASC
  </code></pre>
  
  <p>Note that some tags are not referenced by any items, but I still want them included in the results (and expecting SIZE(t.items) would be zero).  </p>
  
  <p>However, when this query is executed, it returns only the tags that have items associated with it, ignoring tags that are not referenced by any items. The generated SQL from JPA is:</p>
  
  <pre><code>SELECT t0.id as a1, t0.NAME AS a2, COUNT(t1.id) FROM tag t0, item_tag_map t2, item t1 WHERE ((t2.tag_id = t0.id) AND (t1.id = t2.item_id)) GROUP BY t0.id, t0.NAME ORDER BY t0.NAME ASC;
  </code></pre>
  
  <p>It is not performing a LEFT OUTER JOIN which is required to get the results I want. If I was writing this in SQL, I would have done something like</p>
  
  <pre><code>select t.id, t.name, count(map.item_id) from tag as t left join item_tag_map as map on map.tag_id = t.id group by t.id, t.name order by t.name ASC;
  </code></pre>
  
  <p>Is there any way to accomplish this in JPQL? Am I doing something wrong or is it a limitation of JPQL (or a bug)? I'm using PostgreSQL v9.2 and EclipseLink v2.4.2</p>
  
  <p>To provide more details... I have 3 SQL tables: item, tag, and item_tag_map. And here are the snippet of the related Java classes:</p>
  
  <pre><code>@Entity
  @Table(name="item")
  public class Item implements Serializable {
      @Id
      @Column(name="id", updatable=false)
      private String id;
  
      @ManyToMany(cascade=CascadeType.ALL)
      @JoinTable(
          name = "item_tag_map", 
          joinColumns = { @JoinColumn(name = "item_id", referencedColumnName = "id", nullable=false) }, 
          inverseJoinColumns = { @JoinColumn(name = "tag_id", referencedColumnName = "id", nullable=false) })
      private List&lt;Tag&gt; tags;
  ...
  
  
  @Entity
  @Table(name="tag")
  @NamedQueries({
      @NamedQuery(name="Tag.findAllStats", query="SELECT t.id, t.name, SIZE(t.items) FROM Tag t GROUP BY t.id, t.name ORDER BY t.name ASC"),
  })
  public class Tag implements Serializable {
      @Id
      @Column(name="id", updatable=false)
      private long id;
  
      private String name;
  
      @ManyToMany(mappedBy="tags", fetch=FetchType.LAZY)
      private List&lt;Item&gt; items;
  ...
  </code></pre>