Using EXISTS

Nesting SELECT Statements › Using EXISTS

Using EXISTS

When you want to retrieve rows from a table based on the existence of rows in another table, use the EXISTS predicate. The EXISTS predicate includes a subquery. If rows in a table meet the selection criteria in the subquery, the outer SELECT statement proceeds. With the EXISTS predicate, you usually use * rather than a column name with SELECT in the subquery for simplicity.

How It's Done

You want to retrieve the names of employees who have a certain level of a certain skill. You need to access both the EXPERTISE and EMPLOYEE tables to do this. Enter:

select emp_lname, emp_fname
       from employee
       where exists
             (select *
                     from expertise
                     where skill_id = 4444
                       and skill_level = '04'
                       and employee.emp_id = expertise.emp_id);

The outer SELECT statement looks at the first row in the EMPLOYEE table and passes the employee ID to the subquery. The subquery then evaluates this row by checking the employee ID against the criteria in the WHERE clause.

The outer SELECT statement and the subquery are connected by comparing common columns in the WHERE clause of the subquery.

The result looks like this:
EMP_LNAME EMP_FNAME
--------- ---------
Thompson Henry

1 row processed

Here's Another

If you want to list all the jobs in which an employee earns more than $65,000, enter:

select job_id, job_title
       from job
       where exists
             (select *
                     from position
                     where salary_amount > 65000
                       and "position".job_id = job.job_id);

Notes

POSITION is the table name and an SQL keyword; therefore, when the POSITION table name is used as an identifier, it must be enclosed in double quotation marks.

As an alternative, you can use an alias for the table name. For example:

select job_id, job_title
       from job
       where exists
             (select *
                     from position p
                     where salary_amount > 65000
                       and p.job_id = job.job_id);

The outer SELECT statement looks at the first row of the JOB table and passes the job ID to the subquery. If the row meets the selection criteria set up by the WHERE clause in the subquery, the row is displayed.

You need to qualify the column names in this example because the name, JOB_ID, is the same in both tables.

The result looks like this:

JOB_ID JOB_TITLE ------ --------- 8001 Vice President 9001 President 6011 Manager - Acctng 4666 Sr Mechanic 5555 Salesperson 4023 Accountant 6004 Manager - HR 5890 Appraisal Spec 6021 Manager - Mktng 9 rows processed