The EXISTS function takes an inner query as an argument and returns TRUE if the inner query returns one or more rows, and returns FALSE if it returns zero rows. This function will be explained using examples, starting with Example 6.71.
Get the last names of all employees who work on project p1:
USE sample; SELECT emp_lname FROM employee WHERE EXISTS (SELECT * FROM works_on WHERE employee.emp_no = works_on.emp_no AND project_no = 'p1');
The result is
The inner query of the EXISTS function almost always depends on a variable from an outer query. Therefore, the EXISTS function usually specifies a correlated subquery.
Let’s walk through how the Database Engine might process the query in Example 6.71. First, the outer query considers the first row of the employee table (Smith). Next, the EXISTS function is evaluated to determine whether there are any rows in the works_on table whose employee number matches the one from the current row in the outer query, and whose project_no is p1. Because Mr. Smith does not work on the project p1, the result of the inner query is an empty set and the EXISTS function is evaluated to FALSE. Therefore, the employee named Smith does not belong to the final result set. Using this process, all rows of the employee table are tested, and the result set is displayed.
Example 6.72 shows the use of the NOT EXISTS function.
Get the last names of all employees who work for departments not located in Seattle:
USE sample; SELECT emp_lname FROM employee WHERE NOT EXISTS (SELECT * FROM department WHERE employee.dept_no = department.dept_no AND location = 'Seattle');
The result is
The SELECT list of an outer query involving the EXISTS function is not required to be of the form SELECT * as in the previous examples. The form SELECT column_list, where column_list is one or more columns of the table, is an alternate form. Both forms are equivalent, because the EXISTS function tests only the existence (i.e., nonexistence) of rows in the result set. For this reason, the use of SELECT * in this case is safe.