Not Exists in Oracle

Question

I have the table(supplier) column(sup_status) contains the value 'A' and 'I' and now i am selecting the value 'A' using not exists. But in real scenario the main query returns the sup_stauts 'A' and 'I' so I need to select the sup_status='A' records using not exists. But result is not coming. I do not want to use not in operator.

For Example

 SELECT SUP_STATUS FROM SUPPLIER

SUP_STATUS
    A
    I

    
    select sup_status from supplier where not exists(select 
    sup_status from supplier where sup_status='I')

Desired Output

  SUP_STATUS
     A


 MAIN QUERY where not exists(select sup_status from supplier
    where sup_status='I')

Answer 1

When you use the query:

select sup_status
from   supplier
where  not exists(
         select sup_status
         from   supplier
         where  sup_status='I'
       )

Then the sub-query is not correlated to the outer query and so the sub-query is searching the table's entire result set to see if there is any row where the supplier status is I. If there exists one row within the entire result set then the query will output nothing.

If you want to correlate the sub-query to the outer query then you need to specify that in the query. For example, if you want to correlate on supplier_name:

select sup_status
from   supplier s
where  not exists(
         select sup_status
         from   supplier x
         where  x.sup_status='I'
         and    s.supplier_name = x.supplier_name
       )

You could also use analytic functions so that you do not have to use a correlated sub-query:

SELECT sup_status
FROM   (
  SELECT sup_status,
         COUNT(CASE sup_status WHEN 'I' THEN 1 END)
           OVER (PARTITION BY supplier_name) AS has_i
  FROM   supplier
)
WHERE  has_i = 0;