Oracle SQL – Correlated Subquery

Last Updated on :September 29, 2019

• In Correlated Subquery, inner query depends on outer query.
• Correlated Subquery is executed once for each row processed by the outer query.
• Correlated Subquery references one or more columns from its outer query.

Example

The following sql can be used to find out customer details who has ordered 2 or more orders since 01-Jan-2015.

Outer query will execute first and based on the result subquery is evaluated. In below subquery value of cu.customer_id is supplied by the  outer query before subquery gets executed.

SELECT customer_id, cust_first_name, cust_last_name
FROM customers cu
WHERE 2 <=
(SELECT COUNT (*)
FROM orders o
WHERE o.customer_id = cu.customer_id
AND O.ORDER_TIMESTAMP >= DATE '2015-01-01');

Output

CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME
7,"Eugene","Bradley"
2,"William","Hartsfield"
3,"Edward","Logan"

Using EXISTS operator with correlated subquery

EXISTS operator is used with subquery to test whether subquery returns at least one row. EXISTS condition is satisfied  and return TRUE when subquery returns at least one or more rows. When subquery does not return any row EXISTS operator returns FALSE.

The following query explains how to fetch customer detail  who has placed order after 01-Jan-2015 using EXISTS operator with subquery.

SELECT customer_id, cust_first_name, cust_last_name
FROM customers cu
WHERE EXISTS
(SELECT cu.customer_id
FROM orders o
WHERE o.customer_id = cu.customer_id
AND O.ORDER_TIMESTAMP >= DATE '2015-01-01');

Using NOT EXISTS operator with correlated subquery

You can use NOT EXISTS operator with subquery when you want test whether subquery does not return any row.
Below query retrieves customer details who has not placed any order after 01-Jan-2015

SELECT customer_id, cust_first_name, cust_last_name
FROM customers cu
WHERE NOT EXISTS
(SELECT cu.customer_id
FROM orders o
WHERE o.customer_id = cu.customer_id
AND O.ORDER_TIMESTAMP >= DATE '2015-01-01');