A subquery in SQL occurs when a query is nested inside another query. They can be used in SELECT, WHERE or FROM clauses and are especially useful when you need to filter or aggregate at a different level from the initial queries results.
Their syntax is as below:
A good use case for subqueries is when you need to fix a value at a specific level of aggregation. For example, if you want to return the average sales of a category while also keeping the details of each item within that category, a subquery can help. In this case, the average sales must be calculated at a higher aggregation level than the individual items, making a subquery necessary to achieve this.
