title: Test for Empty Subquery in Join
body: <p>I have an Events table with a not-null date field. Based on a pivot date and an offset, the query must return the next 2 events.</p>
  
  <p>Here's the query I'm trying to build:</p>
  
  <pre><code>SELECT e.* FROM Events as e
  JOIN (
      -- latest event before pivot date, with offset
      SELECT * FROM Events where date &lt; :dateRef
          ORDER BY date DESC, id DESC LIMIT :offset,1
  ) as eLatest
  -- depending on :dateRef and :offset, eLatest can be empty!
  -- If eLatest is empty, the join should produce all rows from Events table.
  -- "eLatest is null" gives an error... How can I do it?
  ON e.date &gt; eFirst.date  || (e.date = eFirst.date &amp;&amp; e.id &gt;= eFirst.id)
  ORDER BY e.date, e.id LIMIT 2
  </code></pre>
  
  <p>With the following events sequence when sorted by date ASC (no equal dates to keep it simple) :
  e0 - e1 - e2 - ... - e10</p>
  
  <p>here are some desired results:</p>
  
  <ul>
  <li>:dateRef = e2.date, :offset = 0 : [e1, e2]</li>
  <li>:dateRef = e2.date, :offset = 1 : [e0, e1]</li>
  <li>:dateRef = e2.date, :offset > 1 : [e0, e1]  &lt;--- Does not work, returns no row</li>
  <li>:dateRef &lt;= e0.date, :offset = ? : [e0, e1]  &lt;--- Does not work, returns no row</li>
  <li>:dateRef = e5.date, :offset = 2 : [e2, e3]</li>
  <li>:dateRef > e10.date, :offset = 0 : [e10]</li>
  <li>:dateRef > e10.date, :offset = 2 : [e8, e9]</li>
  </ul>
  
  <p>The problem I have is that when the subquery <code>eLatest</code> is empty, the join never matches. I can't figure out how to drop the join condition when <code>eLatest</code> is empty.</p>
  
  <p>The final query must be doable in JPQL or JPA criteria (so no UNIONS! I already have a query that works with a UNION and am trying to get rid of it...)</p>