SQL: Introduction to Subqueries
In this post, we will go over the uses of Subqueries.
What is a Subquery?
A subquery is a query made within another larger query. Subqueries can also be called an inner query or a nested query. Since subqueries are nested within a larger query, that larger query is considered an outer query. Another term for subqueries is an inner select and for the outer query, an outer select.
Subqueries are useful because they can help organize or structure each step of a query. The inner is executed first, and its results are provided to the outer query to use. Subqueries can also be found within another subquery and in this case, the innermost query is executed first and provides the results to the immediate outer query, and this process continues until the surface layer. A subquery can return a single value, row, column or a table of observations.
By structuring a query using subqueries, each step can be isolated which helps if there are errors. Subqueries are also commonly used as replacements for complex joins or unions due to increased clarity and reading ease.
Subquery Syntax
The syntax of a subquery depends on which type of query is being executed and where it’s coded. They can be used in 4 different types of queries: Select, Insert, Update, and Delete. The code can be found within the Select clause, From Clause, or most commonly the Where clause.
For this post, we will go over subqueries with a Select query in the Where clause due to this most commonly being used for replacement of complex joins.
SELECT column_1, ..., column_n
FROM Table_1
WHERE expression operator
(SELECT column_1, ..., column_n
FROM Table_2
WHERE condition)Let’s break the syntax down. The first couple lines are exactly like any normal Select query with a list of columns to select from a table.
Then the Where clause is used to filter the observations based on the results of the subquery. The results then can be used for comparisons, using logical operators such as >, <, or =. Or these results can be compared against multiple values using operators such as IN, ANY, or ALL. There is also a unique subquery operator EXISTS, that can check if an expression is included in the results.
The code for the subquery is another SELECT query with all its known rules. The main requirement to note is that each subquery must be within a pair of parentheses. To utilize good practice guidelines, the subquery should be indented for ease of reading. By indenting, it is easier to locate where the subquery begins and ends. Another good practice is to sort the data table only in the final outer query. For this reason, the ORDER BY statement should occur only outside subqueries.
Subquery Example
In our example, there is an online store and we want to know the full names and company, if there is one, of individuals who created orders worth more than $10,000. In an online store, the personal information of the clients and the orders are usually separated into their own tables. So what is our first step?
To create this query, we want to structure it into each of its different steps. Since this is a simple example, there are only two major steps: the information from the orders table where we only want the orders greater than $10,000 and the second step is using the results from the first step to match with the client codes in the clients table.
So first let’s create the subquery where we are looking for client code connected to orders that cost more than $10,000.
SELECT client_id
FROM orders
WHERE orders.cost > 10000;We have completed the first step. Now we want the names and companies of any of the clients that have a matching client code in the resulting table.
SELECT last_name, first_name, company
FROM clients
WHERE clients.id IN
(SELECT orders.client_id
FROM orders
WHERE orders.cost > 10000);We have now completed the full query using the results from a subquery. Now this was a very simple example that could also have been solved by using a single join statement. However, this procedure becomes more useful when there are multiple tables involved in the query.
I hope this post helps your understanding of using subqueries when creating complex queries. Thanks for reading!
For more documentation on Subqueries:
