Introduction

Goal

In this tutorial, we aim to explore the concept of correlated subqueries in SQL (Structured Query Language). Correlated subqueries are a powerful tool that allows us to filter data in our database based on a condition that relies on another part of the query.

Learning Outcomes

By the end of this tutorial, you will be able to:

• Understand the concept of correlated subqueries
• Write and use correlated subqueries
• Process data row-by-row using correlated subqueries

Prerequisites

Before you start, you should have a basic understanding of:
- SQL syntax and commands
- Basic database concepts
- How to write simple SQL queries

Step-by-Step Guide

A correlated subquery is a type of SQL subquery. Unlike a regular subquery (which runs once and returns a single value or a set of values), a correlated subquery runs once for each row processed by the outer query. Each subquery is executed sequentially and can use the results of the outer query.

Example

Let's consider an example where we have a Students table and a Courses table. Each student can be enrolled in multiple courses. We want to find each student who is enrolled in the same courses as a particular student.

SELECT s1.StudentName
FROM Students s1
WHERE EXISTS (
    SELECT 1
    FROM Courses c1
    WHERE c1.StudentID = s1.StudentID
    AND c1.CourseID IN (
        SELECT c2.CourseID
        FROM Courses c2
        WHERE c2.StudentID = 'student123'
    )
);

Code Examples

Example 1: Find Employees with Above Average Salary

Here, we'll find all employees whose salary is above the average salary in their department.

SELECT e1.EmployeeName
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e1.DepartmentID = e2.DepartmentID
);

In this example, the outer query scans the Employees table row-by-row. For each row, the subquery calculates the average salary of the employees in the same department and checks if the employee's salary is greater than this average salary.

Example 2: Find Customers who have made more purchases than the average

This query will find all customers who have made more purchases than the average customer.

SELECT c1.CustomerName
FROM Customers c1
WHERE (
    SELECT COUNT(o1.OrderID)
    FROM Orders o1
    WHERE o1.CustomerID = c1.CustomerID
) > (
    SELECT AVG(o2.OrderCount)
    FROM (
        SELECT COUNT(o3.OrderID) AS OrderCount
        FROM Orders o3
        GROUP BY o3.CustomerID
    ) o2
);

Summary

We have covered the concept of correlated subqueries, how to write them, and how to use them to process data row-by-row. Next, you might want to learn about joins, which are another way to combine data from multiple tables.

Practice Exercises

1. Find all products that are more expensive than the average price of products in their category.
2. Find all employees who have been with the company for fewer years than the average employee in their department.

Solutions

1. Solution to exercise 1:

SELECT p1.ProductName
FROM Products p1
WHERE p1.Price > (
    SELECT AVG(p2.Price)
    FROM Products p2
    WHERE p1.CategoryID = p2.CategoryID
);

1. Solution to exercise 2:

SELECT e1.EmployeeName
FROM Employees e1
WHERE e1.YearsWithCompany < (
    SELECT AVG(e2.YearsWithCompany)
    FROM Employees e2
    WHERE e1.DepartmentID = e2.DepartmentID
);

Remember, practice is key when learning SQL. Try to solve as many problems as you can using correlated subqueries to become more comfortable with the concept.