My primary responsibility is to help different organizations with tuning their SQL Server Performance and here is the link to my most popular service SQL Server Performance Health Check. I often encounter complicated performance problems which are not easy to resolve, however, recently during the lunch break, I was asked by Junior developer a question about writing a subquery from a single table by a Junior DBA at the organizations.
Here is the question which Junior DBA asked – “Have you ever faced a situation where you have a single table and you have to write subquery from the same table get a necessary answer?”
Absolutely. A Subquery (also known as an inner query or nested query) is a query within another SQL query and embedded within the SELECT, FROM or WHERE clause.
Here is a sample query on the sample WorldWideImporters database where I am demonstrating the simple example of a subquery.
The problem statement here is – Find all the OrderID where the average price of the OrderID is above the average price of all the units sold so far.
Here is the query which addresses above problem statement.
SELECT OrderID FROM [WideWorldImporters].[Sales].[OrderLines] WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM [WideWorldImporters].[Sales].[OrderLines])
You can see that we needed to use the subquery to first figure out the average price of all the units available. Once you find out the average price of the unit, you can then put that in the WHERE clause and get the necessary OrderID.
Please note that this is just an example of subquery used in the WHERE clause. Similarly, you can use the subquery in the FROM as well as in the SELECT too.
In the future blog post, we will discuss correlated subqueries.
Reference: Pinal Dave (https://blog.sqlauthority.com)
