title: JPQL, search if value is IN or NULL
body: <p>I have a problem using JPQL. </p>
  
  <p>Here is my query : </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT [columns] FROM Table t 
  WHERE [conditions] AND t.aColumn.aSubColumn IN ([values]) 
  OR t.aColumn IS NULL
  </code></pre>
  
  <p>So to explain it, t contains a column that is a foreign key to another table (so an object). I am trying to see if a field of that table contains some values, but if the foreign key is null, then we don't take it into consideration in the query. How can I achieve that ? </p>
  
  <p><strong>PS :</strong> When I try to run the query, the results are null because the FK is null, so it doesn't find anything in the IN clause. </p>
  
  <p><strong>EDIT : original query</strong></p>
  
  <pre class="lang-java prettyprint-override"><code>@NamedQuery(name = "Commande.findCustom", query = "SELECT c FROM Commande c WHERE "
              + "(c.idChargement LIKE :idChargement OR c.idChargement IS NULL) AND "
              + "(c.codeTransporteur LIKE :codeTransporteur OR c.codeTransporteur IS NULL) AND "
              + "(((c.dateChargementPrevu BETWEEN :dateDebut AND :dateFin) OR "
              + "c.dateChargementPrevu IS NULL) OR "
              + "((c.dateLivraisonPrevue BETWEEN :dateDebut AND :dateFin) OR "
              + "c.dateLivraisonPrevue IS NULL)) AND "
              + "(c.idDernierStatut.idListeStatutsCommande IN "
              + "(SELECT l.idListeStatutsCommande FROM Listestatutscommande l "
              + "WHERE l.idStatut IN :idStatut) OR c.idDernierStatut IS NULL) AND "
              + "(c.raisonSocialeDestinataire LIKE :raisonSociale OR c.raisonSocialeDestinataire IS NULL) AND "
              + "(c.adresseDestinataire LIKE :adresseDestinataire OR c.adresseDestinataire IS NULL) AND "
              + "(c.codeDestinataire LIKE :codeDestinataire OR c.codeDestinataire IS NULL) AND "
              + "(c.codePostalDestinataire LIKE :codePostal OR c.codePostalDestinataire IS NULL) AND "
              + "(c.villeDestinataire LIKE :villeDestinataire OR c.villeDestinataire IS NULL) AND "
              + "(c.paysDestinataire LIKE :codePays OR c.paysDestinataire IS NULL) AND "
              + "c.idPartenaire IN :idPartenaire "
              + "ORDER BY c.dateLivraisonPrevue DESC")
  </code></pre>