Subqueries

Imagine we are tasked with finding all customers who have placed orders totalling more than $100. Performing this task directly might seem challenging, but SQL subqueries make it possible to break this down into smaller, more manageable steps. Subqueries allow us to nest one query inside another, providing a flexible way to retrieve and manipulate data. 

Let's learn how to use subqueries to enhance our data retrieval capabilities. Our focus will be to:

• Understand what subqueries are and their importance.

• Practice creating subqueries with both aggregate and non-aggregate queries.

• Learn how to use subqueries in SELECT, FROM, and WHERE clauses.

• Explore how to use subqueries in conjunction with IN and DISTINCT.

Subqueries in SQL

A subquery is a query nested inside another query. It helps us break complex problems into smaller queries that can be executed step by step. Subqueries can be used in various SQL statements, such as SELECT, INSERT, UPDATE, or DELETE. They act like a temporary table or result set that provides data to the main query. The query to find customers who have placed orders totalling more that $100 can be breakdown as, finding the total amount spent by each customer. Then, verify if the amount is greater than $100 and finally retrieving the customers who have made those purchases.

The subqueries are enclosed in parentheses. By embedding one query inside another, we can dynamically filter, compare, or otherwise work with data based on results that SQL retrieves on-the-fly.

Non-aggregate subqueries

These subqueries return data without performing aggregations. These are particularly useful when we want to compare field values in one table against field values in another table or even the same table. For example, we might use a subquery to find all products belonging to a specific category.