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
sudoadministrative 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:
Next, open the MySQL prompt, replacing sammy with your MySQL user account information:
Create a database named zooDB:
If the database was created successfully, you’ll receive the following output:
Query OK, 1 row affected (0.01 sec)
To select the zooDB database run the following USE statement:
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 theintdata 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 thevarchardata type with a maximum of 30 characters.last_name: uses thevarchardata 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 thevarchardata type with a maximum of 15 characters.membership_type: represents the membership type each guest holds, using thevarchardata type to hold a maximum of 30 characters.membership_cost: stores the cost for various membership types. This column uses thedecimaldata 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 theintdata 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:
Next, insert some sample data into the empty table:
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:
However, a query using this syntax will return an error:
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:
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)
+----------+---------+------------+
| 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:
+------------+-----------+--------------+
| 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:
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:
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:
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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:
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:
Note: Using the DELETE statement to delete the records from your new table, will not delete them from the original table. You can run SELECT * FROM original_table to confirm that all the original records are accounted for, even if they were deleted from your new table.
+----------+------------+------------+-----------------------+-----------------+--------------+
| 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.
