SQL Subqueries Tutorial

Overview

Subquery is a powerful SQL tool. It enables users to do things that are not possible using common statements.

Here are some common use cases of subqueries:

• Filtering data based on a subset of records in another table
• Checking the existence or non-existence of records in another table
• Calculating aggregate statistics based on a subset of records in another table
• Selecting records based on the result of a calculation or function on another table

In this post, I will show you how to use subqueries to get the data you need from your tables.

Let’s get started.

A Simple (and useless) example of using a subquery

To get your feet wet, let’s consider this simple query:

SELECT * FROM orders WHERE customer_id < 2;

This query is quite simple to understand. You want to select the record from the orders table where customer_id < 2.

Now, instead of using < 2, you can use a subquery:

SELECT * FROM orders WHERE customer_id < (SELECT 2) ;

I’ve replaced the number 2 by a simple query: SELECT 2. The query is valid and you can see the result:

While the example is quite useless, you can see how the subquery is working. You can use the return values of a query in the outer query.

Practical Use of Subqueries

Let’s consider some practical use cases with a subquery.

Display customer information for orders placed on a specific date

For example, I would like to get the customers who made purchases on a specific date. The steps I should follow are:

• Get the ids of the customer who made the purchase on that date from the orders table
• Select the users who have the id that matches the list I got from the query above

It’s quite simple to achieve the desired result with a subquery:

SELECT first_name, last_name, email, phone
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date = '2022-01-03'
);

Here I used the IN operator. This operator matches the value (customer_id) to a list. If the value is in the list, it returns true.

Get the total number of orders placed by each customer

You can also use a subquery to get the total order a customer made from the orders table and display the info with the customers’ details:

SELECT first_name, last_name, (
    SELECT COUNT(*)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
) AS order_count
FROM customers;

Nested subqueries

You are not limited to one level of subquery. You can use subquery inside subquery. Here is one example:

SELECT first_name, last_name, email, (
    SELECT SUM(price * quantity)
    FROM order_items
    WHERE order_items.order_id IN (
        SELECT order_id
        FROM orders
        WHERE orders.customer_id = customers.customer_id
    )
) AS order_total
FROM customers;

This query uses two subqueries to calculate the total order total for each customer.
The inner subquery selects the order IDs for each customer.
The outer subquery calculates the sum of the product of the price and quantity columns for all items in those orders.

Using subqueries with JOIN

You can use subqueries with JOIN to get even more insights from your data.

Here are some examples:

Get the number of orders and the total value of those orders for each customer, sorted by total value

SELECT c.first_name, c.last_name, o.order_count, o.total_value
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(price * quantity) AS total_value
    FROM orders
    JOIN order_items ON orders.order_id = order_items.order_id
    GROUP BY customer_id
) o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.total_value DESC;

This looks super complex. However, you can easily understand the query by breaking down each step.

Inside the FROM statement is a SELECT. This SELECT query gets the order count (one row is one order), total value of each order (using SUM). Since the quantity and price are not available on the orders table, I need to join with the order_items to get the data.

After this query, the returned value is:

The outer JOIN query just grabs the data from the customers table to get the name values.

Finally, here is the result of the whole query:

Conclusion

As you can see, using subqueries enables you to create very powerful queries. The examples above are a starting point. You can create every more powerful queries when you know other SQL tools, which I’m going to show you in the next tutorials.

Đạt Trần

I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.