Subselect in Oracle database

An alternative would be

select t01.teste           
from teste t01
inner join 
(
   select isn_teste, min(isn_pessoa) isn_pessoa
   from   tes_teste 
   group by isn_teste
) t04         
  on t04.isn_teste = t01.isn_teste      
left join tes_teste_2 t02                   
  on t01.isn_teste = t02.isn_teste           
 and t02.isn_pessoa = t04.isn_pessoa
where t01.isn_empresa = 666

Another alternative would be to pass the condition to the where

select t01.teste           
from teste t01                
inner join tes_teste_2 t02                   
on t01.isn_teste = t02.isn_teste           
where t01.isn_empresa = 666
  and t02.isn_pessoa = (
         select min(t04.isn_pessoa) 
           from tes_teste t04 
          where t04.isn_teste = t01.isn_teste)

A subquery can return more than one record, so you should not use the = operator instead of in .

A different way to do this query is:

select a.teste
  from teste a,
       tes_teste_2 b
 where a.isn_teste = b.isn_teste
   and b.isn_pessoa in ( select min(c.isn_pessoa)
                           from tes_teste c
                          where c.isn_teste = a.isn_teste )
   and a.isn_empresa = 666;