Subqueries, or nested queries, are used to bring back a set of rows to be used by the parent query. Depending on how the subquery is written, it can be executed once for the parent query or it can be executed once for each row returned by the parent query. If the subquery is executed for each row of the parent, this is called a correlated subquery.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
SELECT e.first_name,
e.last_name,
e.job_title
FROM emp e
WHERE e.dept_no in (SELECT dept_no
FROM dept
WHERE name = 'ADMIN');
In this example, all employee names and job titles will be retrieved for the department 'ADMIN'. Notice the use of the operator in when referring to the subquery. The in operator is used when one or more rows might be returned by a subquery. If the equal operator (=) is used, it is assumed that only one row will be returned. If the equal operator (=) is used and more than one row is returned, Oracle will return an error.
This statement could have been written by directly joining the dept table with the emp table in the main or parent query. Subqueries are sometimes used for performance gain. If the parent query contains a lot of tables, it might be advantageous to break up the WHERE clause into subqueries.
SELECT d.name,
e.first_name,
e.last_name,
e.job_title
FROM emp e,
dept d
WHERE e.dept_no = d.dept_no
AND d.adrs_id = (SELECT adrs_id
FROM ADDRESSES
WHERE adrs_id = d.adrs_id)
ORDER BY d.name, e.job_title, e.last_name;
In this example, all employees with their corresponding departments will be retrieved only for departments that have a valid adrs_id in the addresses table. This is a correlated subquery because the subquery references a column in the parent query.
SELECT d.name,
e.first_name,
e.last_name,
e.job_title
FROM emp e,
dept d
WHERE e.dept_no = d.dept_no
AND not exists (SELECT 'X'
FROM ADDRESSES
WHERE city in ('ROCHESTER','NEW YORK')
AND adrs_id = d.adrs_id)
ORDER BY d.name, e.job_title, e.last_name;
This example will return all departments and employees except where departments are located in 'ROCHESTER' and 'NEW YORK'. SELECT 'X' will return a true or false type answer that will be evaluated by the not exists operator. Any constant could be used here; 'X' is only one example.
A correlated subquery can be easily identified if it contains any references to the parent subquery columns in its WHERE clause. Columns from the subquery cannot be referenced anywhere else in the parent query. The following example demonstrates a non-correlated subquery.
SELECT e.first_name,
e.last_name,
e.job_title
FROM emp e
WHERE e.dept_no in (SELECT dept_no
FROM dept
WHERE name = 'ADMIN');
In this example, all employee names and job titles will be retrieved for the department 'ADMIN'. Notice the use of the operator in when referring to the subquery. The in operator is used when one or more rows might be returned by a subquery. If the equal operator (=) is used, it is assumed that only one row will be returned. If the equal operator (=) is used and more than one row is returned, Oracle will return an error.
This statement could have been written by directly joining the dept table with the emp table in the main or parent query. Subqueries are sometimes used for performance gain. If the parent query contains a lot of tables, it might be advantageous to break up the WHERE clause into subqueries.
SELECT d.name,
e.first_name,
e.last_name,
e.job_title
FROM emp e,
dept d
WHERE e.dept_no = d.dept_no
AND d.adrs_id = (SELECT adrs_id
FROM ADDRESSES
WHERE adrs_id = d.adrs_id)
ORDER BY d.name, e.job_title, e.last_name;
In this example, all employees with their corresponding departments will be retrieved only for departments that have a valid adrs_id in the addresses table. This is a correlated subquery because the subquery references a column in the parent query.
SELECT d.name,
e.first_name,
e.last_name,
e.job_title
FROM emp e,
dept d
WHERE e.dept_no = d.dept_no
AND not exists (SELECT 'X'
FROM ADDRESSES
WHERE city in ('ROCHESTER','NEW YORK')
AND adrs_id = d.adrs_id)
ORDER BY d.name, e.job_title, e.last_name;
This example will return all departments and employees except where departments are located in 'ROCHESTER' and 'NEW YORK'. SELECT 'X' will return a true or false type answer that will be evaluated by the not exists operator. Any constant could be used here; 'X' is only one example.
