Lecture 33: Advanced Subqueries

This lecture covers more advanced uses of subqueries. In the last lecture, we saw how subqueries can be used in the WHERE clause. Now let’s explore two other powerful places where subqueries can live: the FROM clause and the SELECT list.

Subqueries in the `FROM` Clause (Derived Tables)

When you use a subquery in the FROM clause, you are essentially creating a temporary, virtual table for the main query to use. This is often called a derived table or an inline view.

A derived table is useful when you want to perform a join on a set of results that has already been aggregated.

For example, let’s say we want to find the average number of comments per post. We can’t do this in one simple step. We first need to find the number of comments for each post, and then we can find the average of those numbers.

SELECT
    AVG(CommentCount)
FROM (
    -- This is the inner query (derived table)
    SELECT
        COUNT(c.CommentID) AS CommentCount
    FROM Posts p
    LEFT JOIN Comments c ON p.PostID = c.Post_ID
    GROUP BY p.PostID
) AS PostCommentCounts;

Let’s break this down:

1. The Inner Query: This query runs first. It calculates the number of comments for each post and returns a temporary table (which we alias as `PostCommentCounts`) with a single column, `CommentCount`.
2. The Outer Query: This query then runs on the results of the inner query. It simply calculates the average of the values in the `CommentCount` column of our derived table.

Subqueries in the `SELECT` List (Scalar Subqueries)

You can also place a subquery directly in the SELECT list. This is called a scalar subquery. It is only allowed if the subquery is guaranteed to return exactly one column and one row.

This is useful for fetching a single piece of related data without needing a full JOIN.

Let’s list all of our posts, and for each post, also show the name of its author. We could use a JOIN, but we can also use a scalar subquery.

SELECT
    p.Title,
    (SELECT a.AuthorName FROM Authors a WHERE a.AuthorID = p.Author_ID) AS Author
FROM Posts p;

For each row in the `Posts` table that the outer query processes, the inner query runs and fetches the single `AuthorName` that corresponds to that post’s `Author_ID`.

While this works, a JOIN is usually more efficient for this specific task. However, scalar subqueries can be very useful in more complex scenarios.

In the Next Lecture…

We will introduce the concept of database normalization. Normalization is the process of organizing the columns and tables in a relational database to minimize data redundancy and improve data integrity. It’s the formal theory behind why we designed our blog database the way we did.

Lecture 32: Introduction to Subqueries