Hit enter after type your search item

What is EXISTS operator?

The 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 EXISTS operator to 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:


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 is showing the data in salaries table while 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, only those records from the employees’ tables are retrieved that has 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 does 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 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:


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 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 “NOT EXISTS” operator and see what the result-set is:

The query:


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:


You may learn more about the NOT IN operator in its tutorial.
This div height required for enabling the sticky sidebar