The Easy Way to Write Correlated Sub-query: 5 Examples

Writing correlated subqueries can be challenging for some people, but they are a powerful tool in SQL for performing complex data retrieval and analysis. Here are five examples to help you understand and use them more easily.

Everything you need to know about correlated subquery

• Before we start, let’s briefly explain what a correlated subquery is. A correlated subquery is a subquery that references one or more columns from the outer query, which allows it to be executed once for each row processed by the outer query.
• This makes it possible to perform row-level comparisons and filtering. Let’s dive into the examples:

01. Find Employees with Higher Salaries than Their Managers

SELECT e.employee_name
FROM employees e
WHERE e.salary > (
SELECT m.salary
FROM employees m
WHERE m.employee_id = e.manager_id
);

Here, we’re selecting employees who have a salary greater than that of their respective managers. The inner subquery looks up the manager’s salary for each employee in the outer query.

02. Count Orders for Each Customer

SELECT c.customer_name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

This query counts the number of orders for each customer using a correlated subquery within the main SQL query.

03. Find the Nth Highest Salary

SELECT DISTINCT salary
FROM employees e1
WHERE N = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary >= e1.salary
);

This query retrieves the Nth highest salary from the employee’s table by comparing each salary with the count of distinct ones( greater than or equal).

04. Find Customers with No Orders

SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT *
FROM orders o
WHERE o.customer_id = c.customer_id
);

This query retrieves customers who have not placed any orders by using the NOT EXISTS operator within the correlated subquery.

05. Find Products with Prices Lower than the Average

SELECT product_name
FROM products p
WHERE p.price < (
SELECT AVG(price)
FROM products
);

This query retrieves products with prices lower than the average price of all products in the table.

These examples should help you understand how to write correlated subqueries. Remember that correlated subqueries can sometimes be less efficient than other techniques, so it’s important to consider performance when using them in real-world scenarios.

Related