Table of Contents
- 1 Overview
- 2 A Simple (and useless) example of using a subquery
- 3 Practical Use of Subqueries
- 4 Using subqueries with JOIN
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
- 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;
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:
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.
I build softwares that solve problems. I also love writing/documenting things I learn/want to learn.