Search code examples
sqlsubqueryjpqlnot-exists

Convert a NOT IN SELECT query in JPQL

SELECT TI
 FROM
    Timpot TI 
 WHERE 
    TI.idImpot not in 
 (select TREG.TImpot.idImpot from Tri1342 TRED where TRED.etatRgltImpot='ANNULE' )

I have this request in JPQL but it does not work I think that it is because of the not in select clause and I would like some explanations .Thanks

Solution

  • You can use a NOT EXISTS clause, more efficient, as follow:

    SELECT TI
FROM Timpot TI 
WHERE NOT EXISTS(
    SELECT 1 FROM Tri1342 TRED 
    WHERE TRED.etatRgltImpot='ANNULE' AND TRED.TImport.idImpot = TI.idImport
)