Subquery And Correlated Query In Sql

Subquery and correlated query in SQL are powerful techniques that allow you to embed one SELECT statement inside another, enabling complex data retrieval and manipulation without resorting to procedural code. Understanding how these nested queries work, when to use them, and how they differ from each other is essential for writing efficient, readable SQL statements that can handle real‑world data challenges Small thing, real impact..

Introduction

A subquery (also called an inner query or nested query) is a SELECT statement placed within the WHERE, HAVING, or FROM clause of another SQL statement. In real terms, when the inner query references columns from the outer query, it becomes a correlated subquery, meaning it is executed once for each row processed by the outer query. Think about it: the outer query uses the result of the subquery as a condition, a derived table, or a column value. This dependency creates a tighter coupling between the two queries and often impacts performance, but it also enables row‑by‑row calculations that plain subqueries cannot achieve Not complicated — just consistent..

Types of Subqueries

Subqueries can be classified based on where they appear and how many rows or columns they return.

1. Scalar Subquery

Returns a single value (one row, one column). It can be used anywhere a single expression is allowed, such as in the SELECT list, WHERE clause, or SET clause of an UPDATE.

SELECT employee_id,
       first_name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg_salary
FROM employees;

2. Row Subquery

Returns a single row with multiple columns. Useful with comparison operators that expect a row value, such as = or IN.

SELECT *
FROM employees
WHERE (department_id, salary) = (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

3. Table Subquery (Derived Table)

Returns a result set that can be treated as a temporary table in the FROM clause.

SELECT d.department_name, sub.avg_sal
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) sub ON d.department_id = sub.department_id;

4. Existential Subquery

Uses EXISTS or NOT EXISTS to test for the presence of rows returned by the subquery And that's really what it comes down to..

SELECT e.first_name, e.last_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.employee_id = e.employee_id
      AND o.order_date >= '2023-01-01'
);

Correlated Subqueries Explained

A correlated subquery differs from a regular subquery because its execution depends on the current row of the outer query. The inner query is evaluated repeatedly—once for each candidate row—making it suitable for row‑specific calculations such as running totals, per‑group rankings, or finding the maximum value within a group.

How Correlation Works

1. The outer query fetches a row.
2. The inner query runs, using values from that outer row (often via aliases).
3. The result of the inner query influences whether the outer row is kept, discarded, or how a column is computed.
4. The process repeats for the next outer row.

Because the inner query is executed many times, correlated subqueries can be slower than their non‑correlated counterparts, especially on large tables. That said, modern query optimizers can sometimes rewrite them into joins or apply semi‑join transformations to improve performance.

Example: Finding Employees Earning More Than Their Department Average

SELECT e.employee_id,
       e.first_name,
       e.last_name,
       e.salary,
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id   -- correlation to outer e.department_id
);

Here, the subquery computes the average salary for the same department as the current employee row (e.department_id). The outer query then keeps only those employees whose salary exceeds that department‑specific average.

Example: Calculating a Running Total

SELECT order_id,
       order_date,
       amount,
       (
           SELECT SUM(amount)
           FROM orders o2
           WHERE o2.customer_id = o1.customer_id
             AND o2.order_date <= o1.order_date
       ) AS running_total
FROM orders o1
ORDER BY customer_id, order_date;

The correlated subquery sums amounts for all prior orders of the same customer, producing a cumulative total for each row.

Performance Considerations

While correlated subqueries offer expressive power, they can become a bottleneck. Here are strategies to mitigate performance issues:

• Index the correlated columns: check that columns used in the correlation predicate (e.g., department_id, customer_id) are indexed in the inner query’s table.
• Replace with a join when possible: Many correlated subqueries can be rewritten as a join with an aggregate or window function, which the optimizer can execute more efficiently.
• Use window functions: For running totals, ranks, or moving averages, window functions (SUM() OVER, ROW_NUMBER() OVER) often outperform correlated subqueries.
• Materialize the subquery: If the inner query returns a relatively small, static set, consider storing it in a temporary table or a common table expression (CTE) and joining to it.
• Analyze the execution plan: Use EXPLAIN (MySQL/PostgreSQL) or SHOWPLAN (SQL Server) to see how many times the inner query is invoked and whether indexes are being used.

When to Prefer a Plain Subquery

If the inner query does not reference any column from the outer query, it is evaluated only once. This makes plain subqueries ideal for:

• Supplying a single constant value (e.g., overall average, maximum date).
• Providing a list of values for an IN clause that is static across all rows.
• Creating a derived table that serves as a reusable source for joins.

Because they run once, plain subqueries usually have negligible overhead compared to correlated versions.

Practical Examples

Example 1: Finding Products Priced Above the Category Median

SELECT p.product_id,
       p.product_name,
       p.price,
       c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE p.price > (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)
    FROM products
    WHERE category_id = p.category_id   -- correlated to outer category
);

Note: PERCENTILE_CONT is a window/aggregate function; the subquery still correlates to compute the median per category.

Example 2: Identifying Customers Who Have Placed Orders in Every Month of a Year

SELECT c.customer_id,
       c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM generate_series(
        DATE_TRUNC('year', CURRENT_DATE)::date,
        DATE_TRUNC('year', CURRENT_DATE)::date + interval '11 months',
        interval '1 month'
    ) AS m(month_start)
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders o