Course contentsShow
← Data Science
Lesson 976 of 2,14525. SQL Subqueries and CTEsFree lesson

Basic Correlated Subquery Syntax

Understand the syntax structure and how the inner query references outer query columns using aliases.

Basic Correlated Subquery Syntax

You'll learn: How to write correlated subqueries by referencing outer query columns inside the inner query using table aliases.

The Core Idea

A correlated subquery differs from a regular subquery because it references columns from the outer query. To make this work, you must use table aliases to distinguish between the outer and inner query's tables. The subquery executes repeatedly—once for each row processed by the outer query—using that row's values.

The syntax pattern looks like this:

SELECT outer.column1, outer.column2
FROM table_name AS outer
WHERE outer.column3 = (
    SELECT aggregate_function(inner.column4)
    FROM table_name AS inner
    WHERE inner.some_column = outer.some_column  -- correlation point
);

Notice the key parts:

  • Outer query alias (outer): identifies columns from the main query
  • Inner query alias (inner): identifies columns from the subquery
  • Correlation condition (inner.some_column = outer.some_column): links the two queries

Why Aliases Matter

Without aliases, SQL wouldn't know whether you're referring to the outer table's column or the inner table's column—especially when you're querying the same table in both places (common in correlated subqueries).

Think of it like nested loops in programming: the outer loop provides a value, and the inner loop uses that value in its own processing.

Simple Example Structure

SELECT e1.employee_name, e1.salary
FROM employees AS e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department = e1.department  -- references outer row
);

Here, for each employee (e1), the subquery calculates the average salary in that employee's department.

Key Takeaway: Correlated subqueries use table aliases to reference outer query columns inside the inner query, creating a row-by-row dependency where the subquery executes once per outer row.