Ayaz Memmedov

Correlated Subqueries
A correlated subquery is a type of subquery in SQL that depends on the outer query for its values. Unlike
a simple subquery, which runs independently and returns a result set to the main query, a correlated
subquery executes once for every row processed by the outer query.

1. Dependency: The subquery references columns from the outer query.

2. Execution: The subquery is executed repeatedly, once for each row in the outer query.
3. Performance: Typically slower than simple subqueries because of the repeated execution.
In below syntax
SELECT column1, column2, ...
FROM table1 alias1
WHERE columnX operator (
SELECT aggregate_function(columnY)
FROM table2 alias2
WHERE alias2.columnZ = alias1.columnA
);
Example: Find Employees Earning More than Their Department's Average

SELECT e.employee_id, e.salary, e.department_id

FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
Explanation:

 The outer query retrieves employees.

  The correlated subquery calculates the average salary for the department of the current
employee (e.department_id).
 For each employee in the outer query, the subquery runs to compare their salary against
their department's average.

Example2:

SELECT d.department_id, d.department_name

FROM departments d

WHERE EXISTS (

SELECT 1

FROM employees e

WHERE e.department_id = d.department_id

AND e.salary > 50000

);

Explanation:

 The subquery checks for employees in each department with a salary > 50,000.
 EXISTS returns TRUE if the subquery finds at least one matching row.

Comparison of Correlated Subqueries with Other

Methods and Their Advantages
1. Correlated subqueries vs simple subqueries
Simple subqueries executes independently of the outer query and runs only once, returning a static
result set. Correlated subqueries depends on the outer query and runs repeatedly, once for each
row processed by the outer query.

Advantages of Correlated Subqueries:

1. Dynamic Results: Each execution adapts to the current row in the outer query, providing
tailored results.
 2. Concise Queries: You can express complex filtering or calculations inline without
needing additional steps.
3. Contextual Accuracy: Operates on data within the specific context of the outer query's
row

Simple subqueries:

SELECT employee_id, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated subqueries:

SELECT e.employee_id, e.salary

FROM employees e

WHERE e.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department_id = e.department_id

);

2. Correlated Subqueries vs Joins

Join combines data from two or more tables in a single operation. Executes once for the
entire dataset.
Correlated Subquery works row-by-row, using a subquery that executes repeatedly for each
row.

Advantages of Correlated Subqueries:

1. Readability: Easier to understand for certain row-specific filtering conditions.

2. Focused Output: Avoids combining all rows like a join, only returning data relevant to the
condition.
3. Efficiency for Small Datasets: Can perform better than joins for small, targeted operations.

Using join

SELECT e.employee_id, e.salary

FROM employees e

JOIN (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

) dept_avg

ON e.department_id = dept_avg.department_id

WHERE e.salary > dept_avg.avg_salary;

Using Correlated subqueries

SELECT e.employee_id, e.salary

FROM employees e

WHERE e.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department_id = e.department_id

);

4. Correlated Subqueries vs Common Table Expressions (CTEs)

CTE creates a temporary result set that can be reused in multiple parts of a query.

Correlated Subquery embeds the logic directly into the main query, with no reusable
intermediate results.

Advantages of Correlated Subqueries:

1. Simpler Syntax: For single-use logic, a correlated subquery eliminates the need to define and
refer to a temporary table.
2. Inline Execution: Executes directly within the query, avoiding the overhead of defining a
separate CTE.

Using CTE
WITH DeptAvg AS (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

SELECT e.employee_id, e.salary

FROM employees e

JOIN DeptAvg da

ON e.department_id = da.department_id

WHERE e.salary > da.avg_salary;

Using correlated subquery

SELECT e.employee_id, e.salary

FROM employees e

WHERE e.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department_id = e.department_id

);

TASKS
1. You are tasked with identifying the products that have a total sales quantity higher than the average
sales quantity of their respective categories. You must have products table with (product_id,
product_name, category_id) and sales with (sale_id,product_is,quantity).

Solution:
SELECT

p.product_name,

p.category_id,

SUM(s.quantity) AS total_sales_quantity

FROM products AS p

JOIN sales AS s ON p.product_id = s.product_id

GROUP BY p.product_id, p.product_name, p.category_id

HAVING SUM(s.quantity) > (

SELECT AVG(sq.quantity)

FROM products AS pq

JOIN sales AS sq ON pq.product_id = sq.product_id

WHERE pq.category_id = p.category_id

);

Firstly we join products and sales for get quantity. Then group by product_id for calculate the total sales
quantity for each product. We need use having for filter. Create correlated subquery The subquery
dynamically calculates the average sales quantity (AVG(sq.quantity)) for the category of the current
product (p.category_id). It runs for each group in the outer query, comparing the product's total
sales to the category's average.

2. You are working for an online retail store. You need to analyze daily sales trends by identifying which
days had above-average sales for a specific month. You have sales table with
(sale_id,sale_date,product_id,quantity,total_price). Specific month is November.

Solution:

SELECT

DATE(sale_date) AS sale_day,

SUM(total_price) AS daily_sales

FROM sales

WHERE EXTRACT(MONTH FROM sale_date) = 11 AND EXTRACT(YEAR FROM sale_date) = 2024

GROUP BY DATE(sale_date)
HAVING SUM(total_price) > (

SELECT AVG(daily_total)

FROM (

SELECT

DATE(sale_date) AS sale_day,

SUM(total_price) AS daily_total

FROM sales

WHERE EXTRACT(MONTH FROM sale_date) = 11 AND EXTRACT(YEAR FROM sale_date) = 2024

GROUP BY DATE(sale_date)

) AS subquery

ORDER BY daily_sales DESC;

Firstly focused sales data November 2024 for it using extract. Then groups by Date(sale_date) and
calculate Sum(total_price). Inside subquery Computes the average daily sales (AVG(daily_total)) for
November 2024. Use Having for filters days where the daily sales are higher than the average daily sales
for the month. Use Order by for sorts the results in descending order of daily sales.

3. Write a SQL query to identify customers whose most recent purchase (last purchase) total was higher
than their average purchase amount. The goal is to find customers who made a significantly larger
purchase compared to their usual spending. Use customer with(customer_id,contact_name) and
purchase(customer_id, purchase_date, total_price).

Solution:
SELECT
c.contact_name,
p.total_price AS last_purchase_total
FROM purchase p
JOIN customer c
ON p.customer_id = c.customer_id
WHERE p.purchase_date = (
SELECT MAX(lp.purchase_date)
FROM purchase lp
WHERE lp.customer_id = p.customer_id
)
AND p.total_price > (
SELECT AVG(ap.total_price)
FROM purchase ap
WHERE ap.customer_id = p.customer_id
);
Firstly we need join customer and purchase then use correlated subquerie find last purchase date and
avg total price. Then use them and finally find result.