SQL: Introduction to Subqueries

In this post, we will go over the uses of Subqueries.

What is a Subquery?

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

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

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:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store