Advanced SQL - Understanding Subqueries

Introduction

A subquery is a query within another query. Think of it like using the result of one question to answer another question.

Why Subqueries Matter?

They’re useful when you need to:

• Compare results to aggregates
• Filter based on other queries
• Create derived calculations
• Use results from one query in another

Main Content: Understanding Subqueries

Let’s use these simple tables:

-- Create simple tables
CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50),
    grade INT
);

CREATE TABLE assignments (
    assignment_id INT,
    student_id INT,
    score INT
);

-- Insert sample data
INSERT INTO students VALUES
(1, 'Alice', 10),
(2, 'Bob', 10),
(3, 'Charlie', 11),
(4, 'David', 11);

INSERT INTO assignments VALUES
(1, 1, 85),
(2, 1, 90),
(3, 2, 78),
(4, 2, 88),
(5, 3, 92),
(6, 3, 95),
(7, 4, 85),
(8, 4, 89);

Example 1: Simple Subquery in WHERE

Let’s find students with above-average scores:

SELECT 
    student_name,
    grade,
    score
FROM students s
JOIN assignments a ON s.student_id = a.student_id
WHERE score > (
    SELECT AVG(score)
    FROM assignments
);

This query:

1. Calculates average score (subquery)
2. Finds scores above that average
3. Shows student details

Results:

student_name | grade | score
-------------|-------|-------
Alice        | 10    | 90
Charlie      | 11    | 92
Charlie      | 11    | 95
David        | 11    | 89

Example 2: Correlated Subquery

Let’s find each student’s highest score:

SELECT 
    s.student_name,
    (SELECT MAX(score)
     FROM assignments a
     WHERE a.student_id = s.student_id) as highest_score
FROM students s;

This query:

1. Takes each student
2. Finds their highest score
3. Shows both together

Results:

student_name | highest_score
-------------|---------------
Alice        | 90
Bob          | 88
Charlie      | 95
David        | 89

Example 3: Derived Table (Subquery in FROM)

Let’s analyze average scores by grade:

SELECT 
    grade,
    AVG(avg_student_score) as grade_average
FROM (
    SELECT 
        s.student_id,
        s.grade,
        AVG(a.score) as avg_student_score
    FROM students s
    JOIN assignments a ON s.student_id = a.student_id
    GROUP BY s.student_id, s.grade
) student_averages
GROUP BY grade;

This query:

1. Calculates each student’s average (inner query)
2. Groups those averages by grade (outer query)
3. Shows grade-level averages

Results:

grade | grade_average
------|---------------
10    | 85.25
11    | 90.25

Best Practices

1. Readability

   • Indent subqueries clearly
   • Use meaningful aliases
   • Break complex queries into steps

2. Performance

   • Keep subqueries simple
   • Consider joins when possible
   • Avoid unnecessary subqueries

3. Types of Subqueries

   • WHERE clause subqueries
   • SELECT clause subqueries
   • FROM clause (derived tables)
   • Correlated subqueries

4. Common Uses

   • Filtering with aggregates
   • Complex calculations
   • Dynamic criteria
   • Multi-step analysis

5. Things to Remember

   • Subqueries in WHERE need single values
   • Correlated subqueries reference outer query
   • Derived tables need aliases
   • Order of execution matters

Remember:

• Subqueries run for each outer query row
• Keep them simple and focused
• Use meaningful names
• Consider performance

Common Mistakes to Avoid:

1. Too many nested levels
2. Incorrect subquery results
3. Missing aliases
4. Unnecessary complexity

Happy querying! 🚀