Correlated Subqueries

Introduction

In SQL, subqueries are queries nested inside another query. These can be categorized into two types: non-correlated and correlated subqueries. Non-correlated subqueries are independent of the outer query and can be executed before or after the outer query. On the other hand, correlated subqueries depend on the outer query for their execution; they are evaluated once for each row processed by the outer query.

This tutorial will explore correlated subqueries, explaining how they work, when to use them, and providing practical examples to illustrate their application in SQL queries.

Concept

A correlated subquery is a subquery that references columns from the outer query. It executes once for each row of the outer query, using the values from the current row of the outer query to determine which rows to process in the subquery. This means that the result of the subquery can vary depending on the context provided by the outer query.

Key Characteristics

1. Dependency: The subquery depends on the outer query for its execution.
2. Row-by-Row Execution: The subquery is executed once for each row processed by the outer query.
3. Performance Considerations: Correlated subqueries can be less efficient than non-correlated ones due to their repeated execution.

When to Use Correlated Subqueries

Correlated subqueries are useful in scenarios where you need to filter or aggregate data based on conditions that involve related tables or columns. Some common use cases include:

• Finding records that meet specific criteria relative to other records.
• Calculating aggregated values for each row of the outer query.

Examples

Let's explore some practical examples to understand how correlated subqueries work in SQL.

Example 1: Find Employees with Above-Average Salary in Their Department

Suppose you have an employees table with columns employee_id, name, department_id, and salary. You want to find all employees whose salary is above the average salary of their respective department.

SELECT e.employee_id, e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

In this example:

• The outer query selects all columns from the employees table.
• The correlated subquery calculates the average salary for each department by filtering the employees table based on the department_id of the current row in the outer query.

Example 2: Find Customers with Orders Above a Certain Amount

Consider an e-commerce database with customers and orders tables. You want to find all customers who have placed orders with a total amount greater than $1000.

SELECT c.customer_id, c.name
FROM customers c
WHERE 1000 < (
    SELECT SUM(o.amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Here:

• The outer query selects the customer_id and name from the customers table.
• The correlated subquery calculates the total order amount for each customer by filtering the orders table based on the customer_id of the current row in the outer query.

Example 3: Find Employees with No Orders

Suppose you have an employees and an orders table, and you want to find all employees who have not placed any orders.

SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.employee_id = e.employee_id
);

In this example:

• The outer query selects the employee_id and name from the employees table.
• The correlated subquery checks if there are any orders associated with each employee by filtering the orders table based on the employee_id of the current row in the outer query. If no orders exist, the NOT EXISTS condition returns true.

What's Next?

In this tutorial, we explored correlated subqueries and their use cases. Understanding how to effectively use correlated subqueries can significantly enhance your ability to write complex SQL queries.

Next, you might want to explore other advanced SQL concepts such as UNIONs, which allow you to combine the results of multiple SELECT statements into a single result set. This will help you further expand your SQL skills and tackle more sophisticated data manipulation tasks.

Feel free to practice these examples and experiment with different scenarios to deepen your understanding of correlated subqueries.