What is a SQL subquery?

Imagine you’re a manager at a large retail store and need to find out which products have sold more than the average number of units in the last month. You decide to use SQL to analyze your sales data and answer this question. This is a scenario where a SQL subquery can be quite helpful. 

A subquery, also known as a nested query or inner query, is a query that is embedded within another SQL query. It is a powerful feature that allows you to perform operations on a set of records and then use the results in another query. 

The purpose of a subquery is to filter, calculate, or aggregate data before using it in the main query. This enables you to use the results of one query as input for another, allowing for more complex and sophisticated data analysis. 

Think of a subquery as a recipe within a recipe. When you’re cooking a complex dish, you may need to prepare a sauce or a side dish first, which can be considered a sub recipe. Once the sauce or side dish is ready, you can use it in the main recipe. Similarly, a subquery prepares and filters data, which can then be used by the main query to accomplish its goal. 

Let’s return to the retail store scenario. You want to find out which products have sold more than the average number of units in the last month. Here’s a query that uses a subquery to help answer this question: 

Let’s break down this query:

1. The main query retrieves the ‘product_id,’ ‘product_name,’ and ‘units_sold‘ columns from the ‘sales‘ table.
2. The subquery calculates the average number of units sold in the last month. It selects the AVG(units_sold) from the ‘sales‘ table and filters the results by ‘sale_date‘ to only include sales within the last month. 
3. The main query then filters the results by only showing products where ‘units_sold‘ is greater than the average calculated by the subquery.

In this example, the subquery is used to calculate the average number of units sold in the last month, which is then used as a filter in the main query to display only the products that have sold more than the average number of units. This allows you to easily identify the top-selling products in your retail store.