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.
Subquery Fundamental
A subquery is a ‘Select’ query placed inside another query or SQL statement. A subquery can be used with a SELECT, INSERT, UPDATE, or DELETE statement and is generally placed within a WHERE, HAVING, or FROM clause of the outer or main query.
The subqueries can generally take one of the below formats:
WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL] (subquery)
WHERE [NOT] EXISTS (subquery)
The general syntax of subquery is:
SELECT column_name(s)
FROM table_name
WHERE column_name operator
(SELECT column_name
FROM table_name
WHERE condition);
Rules for Subqueries
- The subquery is generally included after the WHERE clause, HAVING clause or FROM clause of the outer query. Subqueries can also be used in a ‘Select’ statement to get a column value dynamically.
- Subquery is always nested within a parenthesis ‘()’.
- Subqueries can be used with another subquery, as nested queries.
- A subquery must contain the SELECT query and the FROM clause with ‘WHERE’ condition.
- Subqueries that need to return more than one row should be used with multiple value operators like IN, ALL, ANY or EXISTS.
- The datatypes like ntext, text, and image columns are not allowed in the select list of subqueries.
- ORDER BY command can not be used in a subquery except when TOP is used.
- In a subquery the COMPUTE and INTO clauses can not be used.
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:
Products Table
Orders Table:
Orders 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:
Scalar Subquery example
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:
Single row subquery example output
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:
Multiple row subquery example output
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:
Correlated subquery output
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:
Nested Subqueries output
Benefits of Using Subqueries in SQL Server
- Helps to avoid Code duplication
Subqueries are useful in avoiding Code duplication or not repeating complex logic in multiple places. This reduces the risk of errors and improves code maintainability.
- Subqueries improve Code Readability
Subqueries can break large code and complex logic into easy to understand and more manageable code blocks.
- Useful to perform Complex calculations or data manipulations
Subqueries can be used to perform complex calculations or logic by dividing a single statement query into subqueries to get data from different tables or calculating aggregate values based on dynamic business cases.
- Helps increase Flexibility
Subqueries can be used to filter data dynamically based on the result from other queries, which provides greater flexibility.
- Provides data retrieval from multiple tables
Subqueries are useful when we need to combine data from multiple tables in a single query.
Conclusion
Subqueries are a great tool in SQL Server for writing efficient and powerful SQL queries by accessing data from multiple tables and creating dynamic values in a column from a subquery result. Efficient use of subqueries can be very useful in writing effective SQL Queries.