How To Use Subqueries with the EXISTS Operator

Q

How To Use Subqueries with the EXISTS Operator? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements

✍: FYIcenter.com

A

A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from employees table that there are rows existing in the departments table linked to the employees table with location_id = 1700.

SQL> SELECT first_name, last_name FROM employees e 
  2  WHERE EXISTS (
  3    SELECT * FROM departments d 
  4    WHERE e.department_id = d.department_id
  5    AND d.location_id = 1700
  6  );
FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Neena                Kochhar
Lex                  De Haan
Nancy                Greenberg
Daniel               Faviet
John                 Chen
Ismael               Sciarra
......

2007-04-19, 5173👍, 0💬