select *
FROM (select sites.code_site,
sites.ens,
sites_emprise.ens AS ens_sp
from sites
JOIN spatial.sites_emprise
ON sites.code_site=sites_emprise.codsit) as sitcomp
where ens_sp ILIKE 'oui' 