In this tutorial, we’ll learn and understand how we can utilize a subquery in SQL. A subquery is a query within another query. We can also call it a nested query within a larger query. It must be enclosed with a bracket and mostly used in a WHERE clause.
We can utilize subqueries in SQL when we need to bring data from multiple tables.
For example, let’s say we have these 2 tables.
This is the problem that we need to solve. We need to get the customer name, email ID, and address of the customers who bought in 2022. This is not possible by only using the Customers table because there’s no record of sales or order date column on that table. We have other ways to solve this.
Fetching Data Manually Through Multiple Individual Queries
First, since we have CustomerId from the Sales table, what we can do is to get the customers’ ID of the people who bought in 2022 from the OrderDate column on the Sales table.
Assuming that we now have the customers ID (1, 3, 5, 7, 8, 9…) of the people who bought from 2022, we can then create another query to get the information of those customers through the Customers table based on their ID.
The problem with this method is that we did the process manually. First, we need to get the customers’ ID and manually input them to the next query to get the customers’ information.
Getting Data Automatically Through Subquery In SQL
Instead of manually inputting their IDs, we can do it automatically by using a subquery in SQL. We can use it together with the IN condition to automatically bring the customers’ ID or whatever data that we need to solve a certain problem.
This is an example of what we can do to solve the problem. In this sample query, we’re selecting the name, address, and email ID of the customers from the Customers table based on their customer ID that’s coming from the nested query. In this nested query, the customer ID is coming from the Sales table with an order date of 2022.
With this technique, we don’t need to manually input the customer ID of the people who bought from the year 2022. It will be automatically fetched from the nested query that we’ve created.
Sample Subquery Scenario Via SQL Server Management Studio
Let’s now have a similar scenario and this time, let’s do it within the SQL Server Management Studio (SSMS). In this example, we have two other tables SalesOrderHeader and SalesTerritory.
Our goal is to get all the information on the SalesOrderHeader from a US country region. On the SalesOrderHeader, we don’t have the country region column.
However, we can use the TerritoryID column that’s also available on the SalesTerritory table. That’s where we can also get the country region information which is the CountryRegionCode column.
Again, we can do this manually by getting the TerritoryID first from the SalesTerritory table with a US country region.
We’ll then have the list of TerritoryID under the US country region.
After that, we can use those IDs to get our desired result by manually inputting them on our query.
But instead of manually inputting the IDs, we can do it easily by using a subquery. We just need to replace it with our first query where we’re getting the TerritoryID on the SalesTerritory table with a US country region.
It will then give us the same result as the manual process.
Conclusion
To summarize, we’ve learned the usage and importance of subquery in SQL. We’ve also discussed the proper syntax for creating certain subqueries. We can certainly create multiple queries to create a manual process of getting a certain output. However, we can instead utilize the use of subquery with the WHERE clause and IN condition to automatically fetch certain data from another table.
Hopefully, you’ll be able to use this technique to achieve a more efficient usage of your future queries.
All the best,
Hafiz
***** Related Links *****
SQL Server Download And Installation
SQL For Power BI Users – New Enterprise DNA Course
Querying Data From Multiple Data Sources
