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 for Nested Queries in SQL
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.
Note: Please note that many relational database management systems use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.
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 in SQL 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 provides insights for encouraging members to visit frequently and renew their memberships annually. This example shows the advantage of using a nested query in one statement to get desired results.
Using Nested Queries in SQL with INSERT
With a nested query, you aren’t limited to only embedding it within other SELECT statements. You can use nested queries to insert data by embedding them 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, most data types in this table match those in 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, use INSERT INTO upgrade_guests to clearly specify the data destination. Next, write your nested query with SELECT to retrieve data 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 data was successfully transferred from guests to upgrade_guests with your specified conditions, 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)
The output shows that “Resident” guest membership data was correctly inserted into the upgrade_guests table. Additionally, the new membership_cost has been re-calculated with the 15% discount applied. This operation segments the target audience and makes discounted prices available for prospective new members.
Using Nested Queries in SQL with DELETE
To practice a DELETE nested query, remove frequent visitors to focus on promoting premium pass discounts to less frequent guests.
Start with the DELETE FROM statement to specify the data source, 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 the records were deleted from the upgrade_guests table, then use ORDER BY to sort total_visits ascending:
Note: Deleting records from your new table with the DELETE statement won’t affect the original table. Run SELECT * FROM original_table to verify all original records remain, even if deleted from the new table.
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. Otherwise you would only be able to obtain through running separate queries. Using INSERT and DELETE with nested queries allows data changes in a single step. To learn more about organizing your data, explore our series on How To Use SQL.
