Writing Correlated Subqueries

A correlated subquery references one or more columns in the outer query. The subquery is known as a correlated subquery because the subquery is related to the outer query. You typically use a correlated subquery when you need an answer to a question that depends on a value in each row contained in the outer query.

A Correlated Subquery Example

The following correlated subquery retrieves products that have a price greater than the average for their product type:

  SELECT product_id, product_type_id, name, price   FROM products outer   WHERE price  >  (SELECT AVG(price)   FROM products inner   WHERE inner.product_type_id = outer.product_type_id);  PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE ---------- --------------- ------------------------------ ----------  2 1 Chemistry 30  5 2 Z Files 49.99  7 3 Space Force 9 13.49  10 4 Pop 3 15.99  11 4 Creative Yell 14.99 

Notice I ve used the alias outer to label the outer query and the alias inner for the inner query. The inner and outer parts are correlated using the product_type_id column.

In a correlated subquery, each row in the outer query is passed one at a time to the subquery. The subquery reads each row in turn from the outer query and applies it to the subquery until all the rows from the outer query have been processed . The results from the entire query are then returned.

In the previous example, the outer query retrieves each row from the products table and passes each row to the inner query. Each row is read by the inner query, which calculates the average price for each product where the product_type_id in the inner query is equal to the product_ type_id in the outer query.

Using EXISTS and NOT EXISTS with a Correlated Subquery

You use the EXISTS operator to check for the existence of rows returned by a subquery. Although you can use EXISTS with non-correlated subqueries, you ll typically use it with correlated subqueries. NOT EXISTS does the logical opposite of EXISTS . You use NOT EXISTS when you need to check if rows do not exist in the results returned by a subquery.

Using EXISTS with a Correlated Subquery

The following example uses EXISTS to retrieve employees who manage other employees :

  SELECT employee_id, last_name   FROM employees outer   WHERE EXISTS   (SELECT employee_id   FROM employees inner   WHERE inner.manager_id = outer.employee_id);  EMPLOYEE_ID LAST_NAME ----------- ----------  1 Smith  2 Johnson 

Since EXISTS just checks for the existence of rows returned by the subquery, your subquery doesn t have to return a column: you can just return a literal value. This can improve performance of your query. For example, the following query rewrites the previous example with the subquery returning the literal value 1:

  SELECT employee_id, last_name   FROM employees outer   WHERE EXISTS   (SELECT 1   FROM employees inner   WHERE inner.manager_id = outer.employee_id);  EMPLOYEE_ID LAST_NAME ----------- ----------  1 Smith  2 Johnson 

Using NOT EXISTS with a Correlated Subquery

The following example uses NOT EXISTS to retrieve products that haven t been purchased:

  SELECT product_id, name   FROM products outer   WHERE NOT EXISTS   (SELECT 1   FROM purchases inner   WHERE inner.product_id = outer.product_id);  PRODUCT_ID NAME ---------- -------------------  4 Tank War  5 Z Files  6 2412: The Return  7 Space Force 9  8 From Another Planet  9 Classical Music  10 Pop 3  11 Creative Yell  12 My Front Line 

EXISTS and NOT EXISTS Versus IN and NOT IN

Earlier in the section Using IN with a Multiple Row Subquery, you saw how the IN operator is used to check if a value is contained in a list. EXISTS is different from IN : EXISTS just checks for the existence of rows, whereas IN checks actual values.

Tip  

EXISTS typically offers better performance than IN with subqueries. Therefore you should use EXISTS rather than IN wherever possible.

You should be careful when writing queries that use NOT EXISTS or NOT IN . When a list of values contains a null, NOT EXISTS returns true, but NOT IN returns false. Consider the following example that uses NOT EXISTS and retrieves the product types that don t have any products of that type in the products table:

  SELECT product_type_id, name   FROM product_types outer   WHERE NOT EXISTS   (SELECT 1   FROM products inner   WHERE inner.product_type_id = outer.product_type_id);  PRODUCT_TYPE_ID NAME --------------- ----------  5 Magazine 

Notice one row is returned by this example. The next example rewrites the previous query to use NOT IN; notice no rows are returned:

  SELECT product_type_id, name   FROM product_types   WHERE product_type_id NOT IN   (SELECT product_type_id   FROM products);  no rows selected 

No rows are returned because the subquery returns a list of product_id values, one of which is null. The product_type_id for product #12 is null. Because of this, the NOT IN operator in the outer query returns false and therefore no rows are returned. You can get around this by using the NVL() function to convert nulls to a value. In the following example, NVL() is used to convert null product_type_id values to 0:

  SELECT product_type_id, name   FROM product_types   WHERE product_type_id NOT IN   (SELECT NVL(product_type_id, 0)   FROM products);  PRODUCT_TYPE_ID NAME --------------- ----------  5 Magazine 

This time the expected row appears.