Subqueries in SELECT and WHERE Clauses

What is a Subquery?

At its core, a subquery is a SELECT statement that runs first and returns a result that is then used by the main, or "outer," query. Think of it as answering a preliminary question to help you answer your main question.

For instance, to find "campaigns with a cost higher than the average," you first need to answer the preliminary question: "What is the average cost?" A subquery handles that for you automatically.

This image shows the basic structure. The inner query (the subquery) executes first to get a list of customer_ids, and the outer query uses that list to filter the orders table.

Let's start with a quick video to solidify the concept.

Using Subqueries in the WHERE Clause

The most common use of a subquery is to filter data in the WHERE clause. This is incredibly useful for comparing a record to an aggregated value (like an average) or for filtering based on a list of values from another table.

1. Filtering with a Single Value Result

Let's tackle a classic business analysis question: "Which campaigns are underperforming?" A common way to define this is by finding campaigns with a Cost Per Click (CPC) that is higher than the average CPC across all campaigns.

You can't write WHERE cpc > AVG(cpc) directly in SQL. You need to find the average first, and then use that value for comparison. A subquery does this in one elegant step.

The following video demonstrates this exact logic, breaking it down into simple, manageable steps. While the example uses product prices, the concept is identical to our AdTech scenario.

2. Filtering with a List of Values (IN and NOT IN)

Subqueries are also powerful when they return a list of values. You can then use the IN operator to find records in your main query that match any value in that list.

A common AdTech scenario is finding all the users who have clicked an ad and then analyzing their subsequent behavior. The subquery can generate the list of clicking users.

Conversely, NOT IN is invaluable for finding users who have not performed a certain action, which is crucial for identifying churn risks or untapped user segments. This directly relates to your interview preparation, as questions like "Find users who were active but did not make a purchase" are very common.

This next video uses simple, clear examples to demonstrate subqueries with the IN and NOT IN operators.

SELECT campaign_name
FROM campaigns
WHERE campaign_id NOT IN (SELECT DISTINCT campaign_id FROM conversions);

Using Subqueries in the SELECT Clause

While less common than in the WHERE clause, subqueries in the SELECT clause are useful for adding a calculated value to each row of your result set. This is often used to compare an individual row's value against an overall aggregate.

For example, a report might show each campaign's CPC and, in the next column, the average CPC for all campaigns. This immediately shows which campaigns are above or below the average benchmark.

This video demonstrates exactly this kind of comparison.

Practice for the Real World

Your goal is to be job-ready, and subqueries are a staple of BA technical interviews. The questions are often framed as business scenarios. Let's look at a few practical exercises.

The resource "SQL Scenario Based Interview Questions" is also an excellent one to bookmark. It lists many common interview problems, like question #3 ("Select the 2nd highest salary in the engineering department") and question #7 under the Intermediate section ("Identify which products have prices greater than their own average transaction total"), which are often solved using subqueries.

Conclusion

You've now learned one of the most versatile tools in SQL. Subqueries allow you to build more sophisticated logic and answer deeper business questions in a single, efficient query.

Key Takeaways:

• A subquery is a SELECT statement nested inside another query.
• Subqueries in the WHERE clause are perfect for filtering data based on dynamic conditions, such as comparing to an aggregate (AVG, MAX) or checking for membership in a list (IN, NOT IN).
• Subqueries in the SELECT clause are useful for adding a benchmark or a calculated metric to every row in your result set for easy comparison.
• Thinking in terms of subqueries—breaking a complex problem into a main question and a preliminary question—is a critical skill for any data-focused analyst.

Preview of the Next Lesson:
Now that we can dynamically filter our data with subqueries, what's next? In our next lesson, we will learn about CASE statements. This will allow us to implement conditional logic inside our SELECT clause to create new, customized columns. For example, we could use a CASE statement to label campaigns as 'High Performer', 'Average', or 'Low Performer' based on their metrics, making our reports even more insightful for stakeholders.