title: How to convert SQL into HQL having union and inner joins?
body: <p>I wan to convert this <strong>plain SQL query to HQL</strong>. I have an idea that how to convert SQL queries into HQL queries but my query is containing <strong>UNION</strong> and then inner join. So it become tricky to convert it for me.</p>
  
  <pre><code>select count(distinct T.id_veicolo) from 
  ((select distinct C.id_veicolo from (
  (select distinct id_veicolo from movement
  where id_sede_uscita = 23
  and annullato &lt;&gt; 'true' 
  and (((inizio &gt;= '2019-01-05' and inizio &lt;= '2019-01-06') and fine &gt;= '2019-01-06') 
  or (inizio &lt;= '2019-01-05' and (fine &lt;= '2019-01-06' and fine &gt;= '2019-01-05')) 
  or (inizio &lt;= '2019-01-05' and fine &gt;= '2019-01-06') or (inizio &gt;= '2019-01-05' and fine &lt;= '2019-01-06'))) 
  UNION 
  (select id_veicolo from freeVehicle where id_sede = 23 and inizio &lt;= '2019-01-05' and fine &gt;= '2019-01-06') ) as C) as D 
  inner join (select id from parco_veicoli where targa = 'XXX') as R on D.id_veicolo = R.id) as T
  </code></pre>
  
  <h2>Problem Point</h2>
  
  <p>If there is only <strong>UNION</strong> present in this query then i could convert this query into 2 sub queries but my problem is that after the <strong>UNION</strong> we again using the inner join. Which is the main problem point for me.</p>
  
  <h2>My Requirement</h2>
  
  <p>I want same the result from HQL query as i am getting from this SQL query. I do not want to use SQL query because <strong><em>SQL queries doesn't support caching</em></strong>.</p>