Types of Subqueries in SQL
FREE
Overview
Subqueries are queries that are nested inside another SQL query. They help us target specific rows to perform various operations in SQL. They are used to SELECT, UPDATE, INSERT and DELETE records in SQL. There are different types of SQL subquery, like Single-row subquery, multiple row subquery, multiple column subquery, correlated subquery, and nested subquery. Each type performs different roles, and their result is used depending on the user's requirement.
Introduction
Subqueries are queries that are nested within another SQL query. A subquery can contain more than one query inside it, one after another. They allow us to select specific rows that satisfy certain conditions at the run time. They are also known as the inner query or inner select, and the query that contains them is known as the outer query or outer select.
- Subqueries are nested inside statements like SELECT, INSERT, UPDATE, DELETE, or any other subquery.
- Subqueries are present in the WHERE clause, FROM clause, or HAVING clause of the PARENT SQL query.
- They are used with comparison operators and logical operators like >, <, >=, <>, <=, SOME, ANY, ALL, and IN.
- They execute before the outer query at the run time and pass the result to complete the statement.
- Subqueries are used to compare an expression to the output and check if any row gets selected.
A maximum of 255 subquery levels can be nested in a WHERE clause. The FROM clause has no limit in nesting subqueries. In the real world, we encounter not more than five subqueries. So, 255 is too large to be set as a limit.
Syntax
In this article, we will take you through different types of subqueries and some of their useful examples.
Types of Subqueries in SQL
Imagine that we are famous artists who sell their artworks in art galleries all over the world. Let's see what the tables in our database look like:
Galleries
| id | city |
|---|---|
| 1 | Jaipur |
| 2 | Kolkata |
| 3 | Madhubani |
paintings
| id | name | gallery_id | price |
|---|---|---|---|
| 1 | Patterns | 3 | 5000 |
| 2 | Ringer | 1 | 4500 |
| 3 | Gift | 1 | 3200 |
| 4 | Violin Lessons | 2 | 6700 |
| 5 | Curiosity | 2 | 9800 |
sales_agents
| id | last_name | first_name | gallery_id | agency_fee |
|---|---|---|---|---|
| 1 | Brown | Denis | 2 | 2250 |
| 2 | White | Kate | 3 | 3120 |
| 3 | Black | Sarah | 2 | 1640 |
| 4 | Smith | Helen | 1 | 4500 |
| 5 | Stewart | Tom | 3 | 2130 |
managers
| id | gallery_id |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
Single Row Subquery
Subqueries that return a single row as an output to their parent query are called single-row subqueries. Single-row subqueries are used in a SQL SELECT statement with HAVING clause, WHERE clause, or a FROM clause and a comparison operator. Single-row subqueries are used in the SELECT statement. Let's see it with an example.
The above SQL query displays the average price of all paintings besides their original price. The result looks like this:
| painting | price | avg_price |
|---|---|---|
| Patterns | 5000 | 5840 |
| Ringer | 4500 | 5840 |
| Gift | 3200 | 5840 |
| Violin Lessons | 6700 | 5840 |
| Curiosity | 9800 | 5840 |
The subquery returns the average price of 5840, which is a single value. It gets added to each row of the table.
Note: The inner query is independent of the outer query and gives a meaningful result by running on its own.
Single-row subqueries can also be used with the WHERE clause in the SELECT statement to filter the results of the outer query. Let's see an example of it.
The above SQL query shows the records of the sales agents whose agency fee is greater than the average of all the fees. The subquery present in the statement calculates the average agency fee, which is 2728. The parent statement then uses the returned value to filter out the information of those sales agents with higher-than-average agency fees. The result looks like the following table:
| id | last_name | first_name | gallery_id | agency_fee |
|---|---|---|---|---|
| 2 | White | Kate | 3 | 3120 |
| 4 | Smith | Helen | 1 | 4500 |
Multiple Row Subquery
Subqueries that return multiple rows as an output to their parent query are called multiple-row subqueries. Multiple row subqueries can be used in a SQL SELECT statement with a HAVING clause, WHERE clause, a FROM clause, and a logical operator(ALL, IN, NOT IN, and ANY). Let's explore it using an example.
The above SQL Query calculates the average agency fee of all the sales agents who are not managers. The subquery returns the list of IDs of all managers. Then the outer query filters the table to find the records of the sales agents who are not managers and calculates the average agency fee of those agents. It returns a single average value of the agency fee. The output looks like the following:
| agency_fee |
|---|
| 1885 |
Multiple Column Subqueries
Subqueries that return multiple columns as an output to their parent query are called multiple-column subqueries. Let's see it through an example.
The above SQL Query retrieves the painting with the lowest price. The inner subquery returns the record of the painting with the lowest price. The outer query compares all the records using the IN operator and returns the one with the minimum price.
It gives the following output:
| id | name | gallery_id | price |
|---|---|---|---|
| 3 | Gift | 1 | 3200 |
Correlated Subqueries
Subqueries that return multiple columns as output depending on the information obtained from the parent query are called correlated subqueries. The interdependence of the inner and outer query makes it complicated to understand.
Correlated subqueries can be used in SELECT statements using WHERE and FROM clauses. Let's understand it better using an example.
The above SQL query calculates the number of paintings found in each gallery. The subquery returns a single value for the total number of paintings in the corresponding query. The outer query returns the details of each painting with the city of the art gallery it is present.
The output of the following is:
| city | total_paintings |
|---|---|
| Jaipur | 2 |
| Kolkata | 2 |
| Madhubani | 1 |
You can get the same result using JOIN in SQL. Let's see how!
Note: Generally, JOINS performs faster than subqueries, but you can use them if you find it more intuitive.
Correlated subqueries are also used in SQL statements with WHERE clauses.
Let's check out an example of it.
The above SQL Query shows us the information of those sales agents whose agency fee is greater than or equal to the agency fee of their gallery. The subquery returns the average of the agency fee of the respective sales agent. The outer query returns the information of those sales agents whose agency fee is greater than or equal to the average of the galleries.
The result of the above query is as follows:
| last_name | first_name | agency_fee |
|---|---|---|
| Brown | Denis | 2250 |
| White | Kate | 3120 |
| Smith | Helen | 4500 |
Nested Subqueries
Subqueries that are inside another subquery are called nested subqueries. Subqueries are executed level by level. The innermost is executed first, and then the outer ones. Let's see some examples.
The above SQL query is to display the average price of paintings whose price is greater than 5000 than their original price. The result looks like this:
The output of the following query looks like this:
| painting | price | avg_price |
|---|---|---|
| Patterns | 5000 | 7166.66 |
| Violin Lessons | 6700 | 7166.66 |
| Curiosity | 9800 | 7166.66 |
The above example has three subqueries that are nested subquery, inner subquery, and outer subquery. The code in this example gets departed part by part. Let's break them down one by one to understand the results clearly.
At first, the nested query runs as follows:
This nested query retrieves the price of the paintings whose value is greater than or equal to 5000.
The output of the following query is:
| price |
|---|
| 5000 |
| 6700 |
| 9800 |
The inner subquery looked like this:
After receiving the output from the nested subquery, it internally transforms to:
The subquery returns the average price of paintings of those returned by the nested subquery (i.e., 5000, 6700, 9800). The result of the subquery is a single average value of 7166.66.
Output:
| AVG(price) |
|---|
| 7166.66 |
The outer query is transformed by receiving the result from the subquery and the nested query.
The query internally works as follows:
The outer query returns the name of paintings whose price is greater than or equal to 5000, with their original and average price beside them.
The following is the final output:
| painting | price | avg_price |
|---|---|---|
| Patterns | 5000 | 7166.66 |
| Violin Lessons | 6700 | 7166.66 |
| Curiosity | 9800 | 7166.66 |
Other Examples
Let's consider the following table for the below examples:
| ORD_NUM | ORD_AMOUNT | ADVANCE_AMT | ORD_DATE | CUST_CODE | AGENT_CODE | ORD_DESCRIPTION |
|---|---|---|---|---|---|---|
| 200114 | 3500 | 2000 | 15-AUG-08 | C00002 | A008 | |
| 200122 | 2500 | 400 | 16-SEP-08 | C00003 | A004 | |
| 200118 | 500 | 100 | 20-JUL-08 | C00023 | A006 | |
| 200119 | 4000 | 700 | 16-SEP-08 | C00007 | A010 | |
| 200121 | 1500 | 600 | 23-SEP-08 | C00008 | A004 | |
| 200130 | 2500 | 400 | 30-JUL-08 | C00025 | A011 | |
| 200134 | 4200 | 1800 | 25-SEP-08 | C00004 | A005 | |
| 200108 | 4000 | 600 | 15-FEB-08 | C00008 | A004 | |
| 200103 | 1500 | 700 | 15-MAY-08 | C00021 | A005 | |
| 200105 | 2500 | 500 | 18-JUL-08 | C00025 | A011 | |
| 200109 | 3500 | 800 | 30-JUL-08 | C00011 | A010 | |
| 200101 | 3000 | 1000 | 15-JUL-08 | C00001 | A008 | |
| 200111 | 1000 | 300 | 10-JUL-08 | C00020 | A008 | |
| 200104 | 1500 | 500 | 13-MAR-08 | C00006 | A004 | |
| 200106 | 2500 | 700 | 20-APR-08 | C00005 | A002 | |
| 200125 | 2000 | 600 | 10-OCT-08 | C00018 | A005 |
Subqueries with INSERT statement
Subqueries are used to insert statements to insert selective rows from one table to another.
Syntax
Let us see an example of inserting records in SQL using subqueries.
The above SQL Query will insert those orders into the neworder table whose ADVANCE_AMOUNT is 2000 or 1800 in the orders table.
Subqueries with the UPDATE statement
Subqueries are used in an UPDATE statement to set new values to a column that the returned by the subquery as output.
Syntax
Let's update a record using subqueries in SQL.
The following SQL query updates the ord_date in the neworder table to 15-JAN-10 of the records whose difference between the order amount and advance amount is less than the minimum order amount in the orders table.
Subqueries with DELETE statement
Subqueries are used in a DELETE statement to delete specific rows that is returned as a result of the subquery.
Syntax
Let's delete some rows using subqueries in SQL.
The above code deletes those orders from the neworder table whose advance amount is less than the maximum amount in the orders table.
Conclusion
- In this article, we have learned that there are many types of subqueries.
- The subqueries we use to SELECT, DELETE, UPDATE, and INSERT our records depend on our needs. We can use single-row subqueries for returning single rows, multiple-row subqueries for returning multiple rows, correlated subqueries for returning multiple columns depending on the parent query, and nested subqueries to return a subquery to another one. We have seen examples of all of them one by one.
- Now, it's time for you to practice more and more, to become a great SQL learner!!