SQL Subqueries

A subquery (also called an inner query or nested query) is a SQL query written inside another query. The inner query runs first, and its result is used by the outer query to complete its operation.

Think of it like asking two questions in sequence: first, "Which city has the highest average fees?" Then, second, "Show me all students from that city." A subquery automates this two-step process in a single SQL statement.

The Reference Tables

Students Table

Structure of a Subquery

SELECT column1
FROM table_name
WHERE column2 = (SELECT column FROM table WHERE condition);

The subquery is always enclosed in parentheses. It runs first, and the result is passed to the outer query.

Subquery in the WHERE Clause

Example 1: Find Students Who Pay More Than the Average Fee

SELECT StudentName, Fees
FROM Students
WHERE Fees > (SELECT AVG(Fees) FROM Students);

How it works:

• Inner query: SELECT AVG(Fees) FROM Students → returns 47500
• Outer query becomes: WHERE Fees > 47500

Result:

Example 2: Find the Student Who Pays the Highest Fee

SELECT StudentName, Fees
FROM Students
WHERE Fees = (SELECT MAX(Fees) FROM Students);

Result: Rohit Das — 60000

Example 3: Students Who Are Older Than the Average Age

SELECT StudentName, Age
FROM Students
WHERE Age > (SELECT AVG(Age) FROM Students);

Inner query returns AVG(Age) = 20.83. The outer query returns students older than 20.83.

Result: Priya Mehta (22), Sneha Kapoor (21), Rohit Das (23)

Subquery Returning Multiple Values (Using IN)

When a subquery returns multiple rows, use IN instead of =.

Example 4: Find All Students in the Same City as Rohit Das

SELECT StudentName, City
FROM Students
WHERE City IN (SELECT City FROM Students WHERE StudentName = 'Rohit Das');

Inner query result: 'Kolkata'

Result: Rohit Das (Kolkata) — only one student from Kolkata.

Example 5: Find Students Enrolled in Science or Commerce

SELECT StudentName, Course
FROM Students
WHERE Course IN (
    SELECT DISTINCT Course FROM Students WHERE Fees > 45000
);

Inner query finds all courses where at least one student pays more than 45000 → Commerce, Science. The outer query then returns all students in those courses.

Subquery in the FROM Clause (Derived Table)

A subquery can also appear in the FROM clause, acting like a temporary table. This is called a derived table.

SELECT City, AvgFee
FROM (
    SELECT City, AVG(Fees) AS AvgFee
    FROM Students
    GROUP BY City
) AS CityAverage
WHERE AvgFee > 45000;

How it works:

1. The inner query calculates the average fee per city and labels it as CityAverage.
2. The outer query selects from this derived table and filters cities where AvgFee exceeds 45000.

Result:

Subquery in the SELECT Clause

A subquery can be placed in the SELECT clause to add a calculated value to each row.

SELECT StudentName,
       Fees,
       (SELECT AVG(Fees) FROM Students) AS OverallAvgFee,
       Fees - (SELECT AVG(Fees) FROM Students) AS Difference
FROM Students;

Result:

Correlated Subquery

A correlated subquery references a column from the outer query. It runs once for each row of the outer query, using the outer row's value in the inner query's condition.

SELECT StudentName, Fees, City
FROM Students s1
WHERE Fees > (
    SELECT AVG(Fees)
    FROM Students s2
    WHERE s2.City = s1.City
);

This finds students whose fees are above the average for their own city — not the overall average. The inner query recalculates the city average for each student row.

Subquery vs JOIN

Many problems can be solved with either a subquery or a JOIN. Subqueries are often easier to read and understand, while JOINs are sometimes more efficient for large datasets. For most beginner and intermediate use cases, either approach works well.

Key Points to Remember

• A subquery is always enclosed in parentheses.
• The inner query runs first; its result is used by the outer query.
• Use = when the subquery returns a single value; use IN when it returns multiple rows.
• A subquery in the FROM clause must be given an alias.
• Correlated subqueries reference the outer query's columns and execute row by row.

Summary

Subqueries are queries embedded within other queries. They enable dynamic filtering by using computed values, support multi-step data retrieval, and can appear in WHERE, FROM, and SELECT clauses. Mastering subqueries is essential for writing advanced SQL that goes beyond simple single-table lookups.