title: "where exists" in Hibernate HQL
body: <p>How can I write a &quot;not exists&quot; query in HQL? I am trying to get an HQL not exists query which returns the same results as this Oracle SQL query:</p>
  <pre class="lang-sql prettyprint-override"><code>    select *
      from SCHOOL a
      where not exists (select 1
      from STUDENT b
      where B.SCHOOL_ID=a.id
      and B.STATUS_ID not in (0,1,2,3,4))
  </code></pre>
  <p>My mapping files are below:</p>
  <pre class="lang-xml prettyprint-override"><code>&lt;!-- School.hbm.xml --&gt;
      &lt;class name=&quot;com.companyname.School&quot;
               table=&quot;SCHOOL&quot;
               &gt;
  
        &lt;!-- primary key ommitted --&gt;
      &lt;set name=&quot;students&quot;
           cascade=&quot;all&quot; fetch=&quot;select&quot; lazy=&quot;false&quot; &gt;
          &lt;key column=&quot;SCHOOL_ID&quot; /&gt;
          &lt;one-to-many class=&quot;com.companyname.Student&quot; /&gt;
      &lt;/set&gt;
      &lt;/class&gt;
  
  &lt;!-- Student.hbm.xml --&gt;
     &lt;class
           name=&quot;com.companyname.Student&quot;
           table=&quot;STUDENT&quot;
           &gt;
  
        &lt;!-- primary key ommitted --&gt;
       &lt;many-to-one name=&quot;school&quot; 
                    column=&quot;SCHOOL_ID&quot;
                    class=&quot;com.companyname.School&quot; fetch=&quot;join&quot; lazy=&quot;false&quot;/&gt;
  
       &lt;many-to-one name=&quot;status&quot;
              class=&quot;com.companyname.Status&quot; column=&quot;STATUS_ID&quot; /&gt;
      &lt;/class&gt;
  &lt;!-- Status.hbm.xml. This is a table of lookup data--&gt;
     &lt;class
           name=&quot;com.companyname.Status&quot;
           table=&quot;LK_STATUS&quot;
           mutable=&quot;false&quot;
           &gt;
        &lt;id
              name=&quot;id&quot;
              type=&quot;java.lang.Integer&quot;
              column=&quot;ID&quot;
              &gt;
         &lt;/id&gt;
        &lt;property
              name=&quot;name&quot;
              type=&quot;java.lang.String&quot;
              column=&quot;NAME&quot;
              not-null=&quot;true&quot;
              unique=&quot;true&quot;
              &gt;
        &lt;/property&gt;
     &lt;/class&gt;
  </code></pre>
  <p>I tried the following  tag in my School.hbm.xml file</p>
  <pre class="lang-xml prettyprint-override"><code>    &lt;query name=&quot;myQuery&quot;&gt;
          &lt;![CDATA[
          from School s where not exists from Student st
          where st.school_id=s.id and st.status.id not in (0,1,2,3,4)
          ]]&gt;
      &lt;/query&gt;
  </code></pre>
  <p>and I got this stack trace</p>
  <pre><code>org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in class path resource [applicationContext.xml]: Invocation of init method failed; nested exception is java.lang.NullPointerException
  Caused by: java.lang.NullPointerException
          at org.hibernate.hql.ast.HqlParser.negateNode(HqlParser.java:117)
          at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2378)
          at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2331)
          at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2296)
          at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082)
          at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1858)
          at org.hibernate.hql.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:454)
          at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:708)
          at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
          at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
          at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:248)
          at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
          at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
          at org.hibernate.engine.query.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:77)
          at org.hibernate.engine.query.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:56)
          at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
          at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:402)
          at org.hibernate.impl.SessionFactoryImpl.&lt;init&gt;(SessionFactoryImpl.java:352)
  </code></pre>
  <p>Can somebody let me know what I'm doing wrong?</p>
  <p>Thank you!</p>