Types of Subqueries
There are different types of subqueries used in SQL Server based on usage and data result needed. Let us look into the common types of subqueries in more detail with examples.
Below is the Table Data used in the examples below:
Products Table:
Orders Table:
1. Scalar Subqueries
When a subquery returns only a single data value, which will be a single row with single column, then it is called a Scalar subquery. A simple example of Scalar subquery is below:
Select ProductID, ProductName,CategoryID,Price,
(Select Sum(Quantity) from OrderDetails O where O.ProductID=P.ProductID) As OrderValue
from Products P where CategoryID=2
In the above example a dynamic column value of ‘OrderValue’ created at run time from the result of the subquery given in the ‘Select’ statement.
Output of the above query:
2. Single or Multiple Row Subqueries
Single Row:
In this the subquery returns a Single row value or Multiple rows of values from the Subquery given after the ‘WHERE’ clause of the outer query.
Single Row Example:
Select * from Products Where ProductID = (Select MAX(ProductID) from OrderDetails where OrderID =10250)
In the above example the subquery returns a single value only.
Output for the above single row query:
Multiple Row Example:
Select * from Products Where ProductID IN (Select ProductID from OrderDetails where OrderID =10250)
In the above example with the IN operator the subquery can return multiple rows. There are other operators like NOT IN, ANY, ALL, EXISTS or NOT EXISTS also return multiple rows.
Output for the above multiple row query:
3. Correlated Subqueries
A correlated subquery is a type of subquery that uses the values of the outer query. So the subquery is executed repeatedly once for each row value from the outer query.
Example:
Select * from Products Where ProductID IN (Select ProductID from OrderDetails where OrderID =10255 OR OrderID=10256)
Order by ProductID
Output for the above correlated query:
4. Nested Subqueries
When there are SQL SELECT queries inside a Subquery then it can be called Nested Subqueries. There can be upto 32 levels of nested queries in a single Outer or Main SQL Query statement.
Example:
Select * from Products Where ProductID IN (Select ProductID from OrderDetails where Quantity >
(Select Quantity from OrderDetails where OrderDetailID=10))
Here there are 2 subqueries used, one subquery inside another subquery.
Output for the above Nested Subqueries:
SQL Server Subquery
In SQL Server, Subqueries are a powerful feature used to perform complex queries and combine data from multiple tables or multiple data sets. Subqueries can be used in different business cases and in different scenarios to join data from an inner query with an outer query. In this article let us see what is a subquery, where it can be used, and how to build a subquery.
Contact Us