Lecture 32: Introduction to Subqueries

This lecture introduces the concept of subqueries. We are now moving into Part 4 of our series: Advanced Concepts. The first of these is the subquery, also known as an inner query or a nested query. A subquery is a complete SELECT query that is placed inside another SQL query.

Subqueries are extremely powerful because they allow you to perform a multi-step process in a single query. You can use the result of one query as an input for another.

Subqueries in a `WHERE` Clause

The most common place to use a subquery is in the WHERE clause. This allows you to filter the results of the main (outer) query based on the results of the inner query.

Let’s go back to our blog database. Imagine we want to find all the posts written by ‘Alice Wonder’. We could do this in two steps:

1. Find the `AuthorID` for Alice Wonder: SELECT AuthorID FROM Authors WHERE AuthorName = 'Alice Wonder'; (This returns 1).
2. Find all posts with that `AuthorID`: SELECT Title FROM Posts WHERE Author_ID = 1;

A subquery lets us combine these two steps into one. The inner query gets the ID, and the outer query uses that ID.

SELECT Title
FROM Posts
WHERE Author_ID = (SELECT AuthorID FROM Authors WHERE AuthorName = 'Alice Wonder');

The database executes the inner query first. It returns the value `1`. Then, the outer query effectively becomes SELECT Title FROM Posts WHERE Author_ID = 1;, giving us our final result.

Subqueries with the `IN` Operator

What if the inner query returns more than one value? For example, what if we want to find all posts written by authors whose names start with the letter ‘A’? The inner query might return multiple IDs.

In this case, you can’t use the = operator. Instead, you use the IN operator, which checks if a value exists within a list of values.

SELECT Title
FROM Posts
WHERE Author_ID IN (SELECT AuthorID FROM Authors WHERE AuthorName LIKE 'A%');

The inner query runs first, returning a list of all author IDs for authors whose names start with ‘A’. The outer query then finds all posts where the `Author_ID` is in that list.

In the Next Lecture…

Subqueries can be used in more places than just the WHERE clause. In the next lecture, we will explore how to use subqueries in the FROM clause (as a derived table) and in the SELECT list (as a scalar subquery).

Lecture 31 – Combining Results with UNION