Lesson 23 — Queries within Queries

Subqueries

A subquery is a SELECT statement nested inside another SQL statement. They solve problems that cannot be solved in a single flat query — like filtering on aggregated values.

SQL — WHERE subquery 
-- Subquery in WHERE:
SELECT full_name, mark
FROM   learners
WHERE  mark > (SELECT AVG(mark) FROM learners);
-- Returns learners with above-average marks
-- The subquery runs once and produces a single value

-- Subquery with IN:
SELECT full_name, city
FROM   learners
WHERE  learner_id IN (
    SELECT learner_id
    FROM   placements
    WHERE  monthly_salary > 30000
);  -- learners who are earning above R30,000
SQL — Correlated subquery and EXISTS 
-- Correlated subquery — references outer query (runs once per outer row):
SELECT
    l.full_name,
    l.course,
    l.mark,
    (
        SELECT AVG(mark)
        FROM   learners
        WHERE  course = l.course   -- references outer row
    ) AS course_avg
FROM   learners AS l;
-- For each learner, calculates their course's average
-- Useful but can be slow on large tables — consider a JOIN instead

-- EXISTS — returns rows where the subquery finds at least one match:
SELECT full_name
FROM   learners AS l
WHERE  EXISTS (
    SELECT 1
    FROM   placements AS p
    WHERE  p.learner_id = l.learner_id
);  -- learners who have any placement record
SQL — Subquery in FROM and SELECT 
-- Subquery in FROM (derived table / inline view):
SELECT
    course,
    avg_mark,
    CASE
        WHEN avg_mark >= 70 THEN 'High performance'
        WHEN avg_mark >= 50 THEN 'Adequate'
        ELSE 'Needs attention'
    END AS performance_band
FROM (
    SELECT course, ROUND(AVG(mark), 1) AS avg_mark
    FROM   learners
    GROUP  BY course
) AS course_stats
ORDER BY avg_mark DESC;

-- Subquery in SELECT:
SELECT
    full_name,
    mark,
    (SELECT COUNT(*) FROM learners) AS total_learners,
    ROUND(mark / (SELECT AVG(mark) FROM learners) * 100, 1) AS vs_average_pct
FROM   learners
ORDER BY mark DESC;

Practice Task

Your Turn

Write queries using subqueries: (1) learners who scored above the overall average; (2) learners in courses where the average is above 70 (use a FROM subquery); (3) learners who have NOT been placed (using NOT EXISTS — safer than NOT IN when NULLs may exist); (4) the learner with the highest mark per course (correlated subquery).

Common Mistakes

  • NOT IN with a subquery that can return NULL — result is always empty (NULL IN any set is unknown). Use NOT EXISTS instead.
  • Correlated subqueries on large tables — run once per outer row, potentially millions of times. Rewrite as a JOIN.
  • Subqueries in SELECT that return more than one row — error. The subquery must return exactly one value.
  • Deeply nested subqueries — hard to read. Use CTEs (WITH clause) for readability.

Professional Tip

When a correlated subquery is slow, almost always rewrite it as a JOIN or CTE. The query optimiser can use indexes for JOINs far more effectively than for correlated subqueries.

Mini Quiz

When should you use NOT EXISTS instead of NOT IN?