- If that subquery in the EXISTS contains any row, it returns TRUE.
- You may compare the EXISTS operator to SQL IN and = ANY operators.
- You may use the EXISTS operator with the SELECT, DELETE, UPDATE, and INSERT statements.
To understand more about the EXISTS SQL operator, see the examples below with the explanation.
An example of EXISTS in SQL
For our examples, I will use two tables.
The first table stores employee information (sto_employees) and the second table stores the salaries paid to each employee (sto_emp_sal_paid).
See how EXISTS is used with these two tables and I will explain how it worked;
SELECT * FROM sto_employees; SELECT * FROM sto_emp_salary_paid; SELECT * FROM sto_employees WHERE EXISTS (SELECT emp_id FROM sto_emp_salary_paid WHERE emp_id = sto_employees.Id);
In the graphic, you can see three queries data.
- The first table shows the dummy data for illustration in our employee’s table.
- The second shows the data in the salaries table
- The third is the result of our EXISTS query.
In the third recordset with EXISTS query, you can see five records are returned. You may notice, that only those records from the employees’ tables are retrieved that have an employee ID entry in the sto_emp_sal_paid table.
For each record of the outer SELECT statement, the EXISTS runs the subquery. As a record is found, it stops execution and returns the value as TRUE.
In that case, the record from the outer query is displayed finally.
As such, the id=4 and 6 do not exist in the sto_emp_salary_paid table, it is returned as FALSE and so no records are returned from the sto_employees table.
The same result with IN operator
As mentioned earlier, you may compare the EXISTS clause with the IN operator of SQL. See the result below as I used the IN operator in the WHERE clause for the same tables.
For the comparison, the EXISTS query is also executed as in the above example:
The IN and EXISTS queries:
SELECT * FROM sto_employees WHERE EXISTS (SELECT emp_id FROM sto_emp_salary_paid WHERE emp_id = sto_employees.Id); SELECT * FROM sto_employees WHERE id IN (SELECT emp_id FROM sto_emp_salary_paid);
You see, both results-sets are the same. The question is, which operator you should use in various situations?
Moreover, there is an Oracle quote:
“If the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.”
Source: Goes to Oracle docs
The example of NOT EXISTS operator
The NOT EXISTS works opposite to the EXISTS operator i.e. if no rows are returned the “NOT EXISTS” condition is satisfied.
To understand this clause, have a look at almost the same query as in the first example with the exception that I am using the “NOT EXISTS” operator and see what the result-set is:
SELECT * FROM sto_employees WHERE NOT EXISTS (SELECT emp_id FROM sto_emp_salary_paid WHERE emp_id = sto_employees.Id);
The two rows that were omitted in the first example are fetched this time.
The same result can be achieved by using “NOT IN” operator as using this query:
SELECT * FROM sto_employees WHERE id NOT IN (SELECT emp_id FROM sto_emp_salary_paid);
You may learn more about the NOT IN operator in its tutorial.