EXISTS and NOT EXISTS in SQL
For Complete YouTube Video: Click Here
We will try to understand EXISTS and NOT EXISTS in SQL in this class.
The concepts of EXISTS and NOT EXISTS are used in correlated nested queries.
The concepts of correlated nested queries are already discussed in our previous classes.
Table of Contents
EXISTS and NOT EXISTS in SQL
The EXISTS operator is used to test the existence of rows in the results set of the subquery.
If a subquery row value is found, the condition is flagged TRUE, and the search does not continue in the inner query, and if it is not found, the condition is flagged FALSE, and the search continues in the inner query.
Example of EXISTS and NOT EXISTS
To understand this concept, consider the example below.
Find employees who have at least one person reporting to them.
The above query states that we have to extract the company’s supervisors.
To get the output for this query, we need the employee table of the company database, as shown below.
The query to get the output is as shown below.
SELECT outer.Fname, outer.Lname FROM employee outer WHERE EXISTS (SELECT SSN FROM employee inner WHERE outer. ssn = inner.super_ssn);
The inner query compares the ssn of each employee with all the super_ssn values.
If there EXISTS a match with at least a single value, then the inner subquery will produce a TRUE output.
The details of the employee are produced in the output.
The output of the query is as shown below.
Similarly, NOT EXISTS is just the opposite of the EXISTS.
The query with NOT EXISTS is shown below.
SELECT outer.Fname, outer.Lname FROM employee outer WHERE NOT EXISTS (SELECT SSN FROM employee inner WHERE outer. ssn = inner.super_ssn);
If the output of the inner subquery is false, then the output is displayed with the details of the employee.
The query’s output will have all the employees who are not supervisors.
The output of the query is shown below.
