0

Say we have two completely unrelated entities EntityX and EntityY both with column age. How do I in OQL do a join like so

select x,y from EntityX as x full outer join EntityY as y on x.age = y.age

Is this not possible?

thanks, Dean

Dean Hiller
  • 19,235
  • 25
  • 129
  • 212

1 Answers1

1

full outer join should almost never be used. You should use left outer join instead which is supported by JP-QL/OQL : http://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/queryhql.html#queryhql-joins

overmeulen
  • 1,158
  • 6
  • 15
  • yes, this is something I have always used over and over. This however did not answer my question. Is this not possible? – Dean Hiller Feb 19 '13 at 15:48
  • I don't know if it's possible with JP-QL but HQL supports it : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-joins – overmeulen Feb 19 '13 at 15:51
  • JP-QL doesn't seem to support it, I couldn't find any reference to it in the JPQL Language Reference : http://docs.oracle.com/html/E24396_01/ejb3_langref.html – overmeulen Feb 19 '13 at 16:02
  • If you don't want to use Hibernate, you can always "simulate" a FULL OUTER JOIN by doing an union of an INNER JOIN, a LEFT JOIN with right side IS NULL and a RIGHT JOIN with left side IS NULL. – overmeulen Feb 19 '13 at 16:25
  • every example in that link shows the entity being related and has no "on" keyword. Am I missing one? maybe you can paste the exact code as none of that code seems to be doing the thing my example does. The important point being these are unrelated entities and we are just getting stats really on them by joining them on some random column. I do see a full join but that example assumes a relationship there. – Dean Hiller Feb 19 '13 at 16:28
  • Sorry I focused on the FULL OUTER JOIN part and missed your other constraint. If your 2 entities are completely unrelated why not using a cartesian product/cross join : select x,y from EntityX x, EntityY y where x.age = y.age – overmeulen Feb 19 '13 at 16:50
  • turns out I missed that when reading the cross join yesterday(I thought I read it was the inner join but you are right after I looked again). That is what I was looking for. thanks!!!!!! – Dean Hiller Feb 19 '13 at 17:05