How To Use Nested Queries in SQL

Introduction

Structured Query Language (SQL) is used to manage data in a relational database management system (RDBMS). A useful function in SQL is creating a query within a query, also known as a subquery or nested query. A nested query is a SELECT statement that is typically enclosed in parentheses, and embedded within a primary SELECT, INSERT, or DELETE operation.

In this tutorial, you will use nested queries with the SELECT, INSERT, and DELETE statements. You will also use aggregate functions within a nested query to compare the data values against the sorted data values you specified for with the WHERE and LIKE clauses.

Prerequisites

To follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this tutorial were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installed and secured on the server. Follow our How To Install MySQL on Ubuntu 20.04 guide to set this up. This guide assumes you’ve also set up a non-root MySQL user, as outlined in Step 3 of this guide.

To practice using nested queries in this tutorial, you’ll need a database and table loaded with sample data. If you do not have one ready to insert, you can read the following Connecting to MySQL and Setting up a Sample Database section to learn how to create a database and table. This tutorial will refer to this sample database and table throughout.

Connecting to MySQL and Setting up a Sample Database

If your SQL database runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Next, open the MySQL prompt, replacing sammy with your MySQL user account information:

mysql -u sammy -p

Create a database named zooDB:

CREATE DATABASE zooDB;

If the database was created successfully, you’ll receive the following output:

Output
Query OK, 1 row affected (0.01 sec)

To select the zooDB database run the following USE statement:

USE zooDB;

Output
Database changed

After selecting the database, create a table within it. For this tutorial’s example, we’ll create a table that stores information about guests who visit the zoo. This table will hold the following seven columns:

• guest_id: stores values for guests who visit the zoo, and uses the int data type. This also serves as the table’s primary key, meaning each value in this column will function as a unique identifier for its respective row.
• first_name: holds the first name of each guest using the varchar data type with a maximum of 30 characters.
• last_name: uses the varchar data type, again at a maximum of 30 characters, to store each guest’s last name.
• guest_type: contains the guest type (adult or child) for each guest using the varchar data type with a maximum of 15 characters.
• membership_type: represents the membership type each guest holds, using the varchar data type to hold a maximum of 30 characters.
• membership_cost: stores the cost for various membership types. This column uses the decimal data type with a precision of five and a scale of two, meaning values in this column can have five digits, and two digits to the right of the decimal point.
• total_visits: uses the int data type to record the total number of visits from each guest.

Create a table named guests that contains each of these columns by running the following CREATE TABLE command:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

Next, insert some sample data into the empty table:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Once you’ve inserted the data, you’re ready to begin using nested queries in SQL.

Using Nested Queries with SELECT

In SQL, a query is an operation that retrieves data from a table in a database and always includes a SELECT statement. A nested query is a complete query embedded within another operation. A nested query can have all the elements used in a regular query, and any valid query can be embedded within another operation to become a nested query. For instance, a nested query can be embedded within INSERT and DELETE operations. Depending on the operation, a nested query should be embedded by enclosing the statement within the correct number of parentheses to follow a particular order of operations. A nested query is also useful in scenarios where you want to execute multiple commands in one query statement, rather than writing multiple ones to return your desired result(s).

To better understand nested queries, let’s illustrate how they can be useful by using the sample data from the previous step. For example, say you want to find all the guests in the guests table who have visited the zoo at a higher frequency than the average number. You might assume you can find this information with a query like the following:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

However, a query using this syntax will return an error:

Output
ERROR 1111 (HY000): Invalid use of group function

The reason for this error is that aggregate functions like AVG() do not work unless they are executed within a SELECT clause.

One option for retrieving this information would be to first run a query to find the average number of guest visits, and then run another query to find results based on that value such as in the following two examples:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

However, you can obtain this same result set with a single query by nesting the first query (SELECT AVG(total_visits) FROM guests;) within the second. Keep in mind that with nested queries, using the appropriate amount of parentheses is necessary to complete the operation you want to perform. This is because the nested query is the first operation that gets performed:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

According to this output, five guests were visiting more than the average. This information could offer useful insights into thinking of creative ways to ensure current members continue to visit the zoo frequently and renew their membership passes each year. Moreover, this example demonstrates the value of using a nested query in one complete statement for the desired results, rather than having to run two separate queries.

Using Nested Queries with INSERT

With a nested query, you aren’t limited to only embedding it within other SELECT statements. In fact, you can also use nested queries to insert data into an existing table by embedding your nested query within an INSERT operation.

To illustrate, let’s say an affiliated zoo requests some information about your guests because they’re interested in offering a 15% discount to guests who purchase a “Resident” membership at their location. To do this, use CREATE TABLE to create a new table called upgrade_guests that holds six columns. Pay close attention to the data types, such as int and varchar, and the maximum characters they can hold. If they do not align with the original data types from the guests table you created in the setting up a sample database section, then you will receive an error when you try inserting data from the guests table using a nested query and the data will not transfer correctly. Create your table with the following information:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

For consistency and accuracy, we’ve kept most of the data type information in this table the same as the guests table. We’ve also removed any extra columns we don’t want in the new table. With this empty table ready to go, the next step is to insert the desired data values into the table.

In this operation, write INSERT INTO and the new upgrade_guests table, so that there’s a clear direction for where the data is being inserted. Next, write your nested query with the SELECT statement to retrieve the relevant data values and FROM to ensure they’re coming from the guests table.

Additionally, apply the 15% discount to any of the “Resident” members by including the multiplication mathematical operation, * to multiply by 0.85, within the nested query statement (membership_cost * 0.85). Then use the WHERE clause to sort for values in the membership_type column. You can narrow it down even further to only results for “Resident” memberships using the LIKE clause and place the percentage % symbol before and after the word “Resident” in single quotes to select any memberships that follow the same pattern, or in this case the same verbiage. Your query will be written as follows:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

The output indicates that there were five records added to the new upgrade_guests table. To confirm the data you requested was successfully transferred from the guests table into the empty upgrade_guests table you created, and with the conditions you specified for with the nested query and the WHERE clause, run the following:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

According to this output from your new upgrade_guests table, the “Resident” related guest membership information from the guest table was inserted correctly. Additionally, the new membership_cost has been re-calculated with the 15% discount applied. As a result, this operation has helped to segment and target the appropriate audience and has the discounted prices readily available to share with these prospective new members.

Using Nested Queries with DELETE

To practice using a nested query with a DELETE statement, let’s say you want to remove any guests that are frequent visitors because you only want to focus on promoting the upgraded premium pass discount to members who aren’t currently visiting the zoo a lot.

Begin this operation with the DELETE FROM statement so it’s clear where the data is being deleted from, in this case, the upgrade_guests table. Then, use the WHERE clause to sort any total_visits that are more than the amount that is specified in the nested query. In your embedded nested query, use SELECT to find the average, AVG, of total_visits, so the preceding WHERE clause has the appropriate data values to compare against. Lastly, use FROM to retrieve that information from the guests table. The full query statement will be like the following:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Confirm those records were successfully deleted from the upgrade_guests table and use ORDER BY to organize the results by total_visits in numerical and ascending order:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

As this output indicates, the DELETE statement and the nested query functioned properly in deleting the specified data values. This table now holds the information for the three guests with less than the average number of visits, which is a great starting point for the zoo representative to reach out to them about upgrading to a premium pass at a discounted price and hopefully encourage them to go to the zoo more often.

Conclusion

Nested queries are useful because they allow you to obtain highly granular results that you would otherwise only be able to obtain through running separate queries. Additionally, using INSERT, and DELETE statements with nested queries provides you with another way to insert or delete data in one step. If you’d like to learn more about how to organize your data, check out our series on How To Use SQL.