What is EXISTS operator?

SQL EXISTS is an operator that is used with the WHERE clause that tests the existence of rows by using a subquery.
  • 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;

Queries:

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);

SQL EXISTS

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);

The result-sets:

SQL EXISTS IN

You see, both results-sets are the same. The question is, which operator you should use in various situations?

If the sub-query used returns very large record-sets then EXISTS is much faster than the IN clause. However, if the recordset is smaller then the IN clause behaves faster.

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:

The query:

SELECT * FROM sto_employees

WHERE NOT EXISTS (SELECT emp_id FROM sto_emp_salary_paid WHERE emp_id = sto_employees.Id);

The result:

SQL NOT EXISTS

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.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!