Search code examples
sqlselectoracle-sqldeveloperexists

How does exists operator work? I don't know why the result happens

I thought that the following SQL statement returns lists salary less than 5000. But I got salaries that are greater than or equal to 5000. I really don't know why. Did I miss something?

select salary 
from employees 
where exists (select 1 from employees e where salary < 5000);
Solution

  • The query inside the exists operator in your query returns at least one row, so the operator evaluates to true. The issue here is that it's not correlated to the outer query in any way, so it returns the same result for every row in the outer query (i.e., always true).

    To get the result you want, you need to correlate between the queries by having a term from the outer query in the inner query's where clause:

    SELECT salary
FROM   employees e1
WHERE  EXISTS (SELECT 1
               FROM   employees e2 
               WHERE  e1.emp_id = e2.emp_id AND salary < 5000);

    But, to be completely honest, you don't really need an exists operator here, and can just query the salary directly:

    SELECT salary
FROM   employees
WHERE  salary < 5000