This article briefly explains how to write a subquery in SQL by providing examples.

Introduction

A SQL query is a command used to request data from tables stored in relational databases. In general, a SQL query contains at least two or more clauses:

1. Select clause: This clause is used to specify the resultset metadata (columns, fixed values, expressions)
2. From clause: This clause is used to specify the data sources we are querying. A data source can be a single table or view, or it can have more complex forms
3. Where clause: This clause is used to specify the data filtering operations needed in the SQL query

The following sections explain how to write a subquery in SQL within the SELECT, FROM, and WHERE clauses.

• Side Note: All examples in this article are made using the Stack overflow 2013 database and SQL Server 2019

Writing subqueries in the SELECT clause

First, we will explain how to write a subquery in SQL within the SELECT clause. Even if writing subquery is supported within the SELECT clause, developers must write their query carefully once they decide to use it since it decreases the query performance.

Let us assume that we need to write a SQL query to retrieve the top ten users in the Stack overflow database and the latest badge earned by each user. Let us consider the following query:

Figure 1 – Writing a subquery within the SELECT clause

Writing a subquery as a column does not mean that the subquery is executed for each row retrieved from the Users table. If we display the estimated execution plan, it will show that the badges data is retrieved then left joined with the Users table.

Figure 2 – Execution plan

Writing subqueries in the FROM clause

In this section, we will illustrate how to write a subquery in SQL within the FROM clause.

Instead of using a table or view name in the FROM clause, we can use a SQL subquery as a data source, noting that assigning an alias is required. Let us try to write the previous query in another way:

Instead of writing the TOP 10 option in the SELECT clause, we decided to force the SQL Server query optimizer to execute the TOP 10 data retrieval operation before joining the Users table with the Badges data, as shown previously.

Figure 3 – Writing a subquery in the FROM clause

In the screenshot below, you can note how the TOP operator is executed directly after scanning the Users clustered index, while in the previous section, it was performed as the last step.

Figure 4 – Execution plan

• Note: This does not mean that the second approach is better than the first one. The example is used only to illustrate the impact of moving the TOP operator into a subquery

Writing subqueries in JOINS

Besides, we can add joins within the FROM clause while using subqueries. Let us use the following example to illustrate how to write a subquery in SQL within the FROM clause when joins are needed.

In the example above, we used a subquery to retrieve the latest ten posts and their creation date. Then we joined the result with the Users table to get the posts owners information.

Figure 5 – Using an SQL subquery within the FROM clause with joins

Writing subqueries in the WHERE clause

To illustrate how to write subquery in SQL within the WHERE clause, we will edit the previous query to retrieve the users who posted the latest ten posts in the Stack overflow database. Let us use the following query:

In this query, we moved the subquery from the FROM clause into the WHERE clause, and we used the IN operator to filter the Users Id based on the subquery result.

Figure 6 – Writing a SQL subquery within the WHERE clause

Alternatives

There are many alternatives of using subqueries in SQL:

1. Using Views: in some cases, views can replace subqueries to make the query looks simpler. This option does not affect or improve the query performance except in the case of indexed views. You can learn more about views in the following article: Learn SQL: SQL Views
2. Using common table expressions (CTE): Common table expressions are an alternative to subqueries. You can learn more about this feature in the following article: CTEs in SQL Server; Querying Common Table Expressions

Summary

This article illustrated how to write a subquery in SQL within the SELECT, FROM, and WHERE clauses. Even if this ability is interesting in structured Query Language (SQL), developers should use it carefully since it may affect the query performance. Besides, it is crucial to create some indexes when needed to increase the querying performance.

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023