What are Subqueries

A subquery is a SELECT statement that is nested within another SELECT statement which return intermediate results

• SQL executes innermost subquery first, then next level

Subqueries fetch data that is used by the main (outer) query.

Subqueries make it easier to retrieve related data without complex joins.

Uses

Select students who have higher GPA than the average of all GPAs

SELECT *
FROM students
WHERE GPA > (
  SELECT AVG(GPA)
  FROM students);

Show average number of students in English or History class

SELECT AVG(number_of_students)
FROM classes
WHERE teacher_id 
IN (
  SELECT id
  FROM teachers
  WHERE subject = 'English' OR subject = 'History'
);

Correlated Subqueries

Computes values in nested query that depend on each row (row-by-row) of the outer query.

This makes correlated subqueries dependent on the outer query.

• Meaning - this subquery cannot run independently because it references columns from outer query

SELECT c.customer_id, c.customer_name,
  (SELECT COUNT(o.order_id)
   FROM Orders o
   WHERE o.customer_id = c.customer_id) AS total_orders
FROM Customers c;

• Outer query selects each customer
• The correlated subquery counts the number of orders for each customer by referencing c.customer_id from the outer query
• The subquery is executed for each row in the Customers table
• total_orders become another column in the result table

Warning: Correlated Subqueries can degrade performance

The correlated subquery is executed repeatedly, once for each row processed by the outer query.

• This makes running correlated subquery expensive for a large database.

Alternative would be to use a JOIN instead.