Learn SQL for Developers/

...

/

Subqueries

Subqueries

Learn about using subqueries in SQL.

We'll cover the following...

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.

Press + to interact
-- Definition for the Categories table
-- CREATE TABLE Categories (
--     CategoryID INT PRIMARY KEY AUTO_INCREMENT,
--     CategoryName VARCHAR(50) NOT NULL UNIQUE
-- );
-- Definition for the Products table
-- CREATE TABLE Products (
--     ProductID INT PRIMARY KEY  AUTO_INCREMENT,
--     ProductName VARCHAR(50) NOT NULL UNIQUE,
--     CategoryID INT,
--     Price DECIMAL(10, 2) NOT NULL,
--     Stock INT NOT NULL,
--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
-- );
-- ------------------------------------------------------------------------------
SELECT ProductName
FROM Products
WHERE CategoryID = (SELECT CategoryID FROM Categories WHERE CategoryName = 'Electronics');

In the code above:

  • Line 21: The inner query retrieves the CategoryID for "Electronics".

  • Lines 19–21: The outer query finds all products in that category. ...

Aggregat