T-SQL provides different ways of “gathering” information from tables. Usually in our practice, we use joins, unions, table expression, etc. But, one of the most interesting ways of getting information from tables is done using Subquery.

Preparation
The below examples require using AdventureWorks2019 from Microsoft. You can download it from here.

Let's get started.

First, what is Subquery?
- Subqueries in T-SQL provide a way for retrieving data which relies on some “statistical” information. In case of using subqueries, looking at a query from a building block perspective, it has two main parts:

    Main/Outer query (what will be filtered)
    Secondary query (subquery, the filtering side)

We use subquery in the WHERE clause. Depending on the subquery, we do some filtering on the main query. Subqueries can return single, multiple or table-based data. For a single response, we will use **WHERE colx = ( response from subquery )** pattern. On the other hand, for multiple responses, our pattern would be WHERE colx IN ( response from subquery).

There are two types of subqueries in T-SQL:
    Self-contained subqueries
    Correlated subqueries

Self-contained subqueries
This type of subquery has no dependency on the outer/main query. They can be easily executed in isolated context.

Here are some use cases on how to use self-contained subqueries:
USE AdventureWorks2019;
Go
--Retrieve all SalesOrderDetail info WHERE :
-- 1)unitprice is greater than average unit price in the same table.

SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.UnitPrice > (SELECT AVG(SOD1.UnitPrice) FROM Sales.SalesOrderDetail AS SOD1)

--2)unitprice is less than or equal to minimum lineTotal's and maximum linetotal's sum dividing to 33;
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOd.UnitPrice <= (SELECT min(SOD1.LineTotal)+max(SOd1.linetotal)/33 FROM Sales.SalesOrderDetail AS SOD1)

--3) DiscountPct for SpecialOffer is less than or equal to 0.30
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN (SELECT SO.SpecialOfferID FROM Sales.SpecialOffer AS SO
WHERE So.DiscountPct <=0.30)

--4) StartDate is 2011-04-01 and EndDate is 2014-01-01 for SpecialOffer
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN (SELECT SO.SpecialOfferID FROM Sales.SpecialOffer AS SO
WHERE So.StartDate >='20110401' AND SO.EndDate <'20140101')

--5) Groupped category's average maxQTY is not null
SELECT * FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.SpecialOfferID IN
(
 SELECT So.SpecialOfferID FROM Sales.SpecialOffer AS SO
 WHERE So.Category IN
 (
  SELECT Category FROM Sales.SpecialOffer AS SO1
  GROUP BY Category
  HAVING AVG(So1.maxQTY) IS NOT NULL))

As you can see from the queries, it is possible to select and execute subquery part of query and get response easily.

Correlated subqueries
This type of subquery has direct dependency on the outer query. So, at least one column from the outer query should participate in the subquery. This will allow us to get “statistical” information related to a given column from the main query.

Here are some use cases on how to use correlated subqueries:
/*
   Retrieve all SpecialOffer information where MinQty is not empty  PER SPECIALOFFERID column
*/
SELECT * FROM Sales.SpecialOffer AS SOD
WHERE SOD.SpecialOfferID IN
(SELECT SO.SpecialOfferId FROM Sales.SpecialOffer AS SO
WHERE SO.SpecialOfferID = SOD.SpecialOfferID AND SO.MinQty IS NOT NULL)

/*
    Show all PurchaseOrderDetail info PER product WHERE listprice is greater than 100
*/
SELECT * FROM Purchasing.PurchaseOrderDetail AS POD
WHERE POD.ProductID IN
(SELECT P.ProductId FROM Production.Product AS P
WHERE P.ProductID = POD.ProductID AND P.ListPrice > 100)

Understanding Subqueries is essential when we need to glean "statistical" information from given or related tables. Self-contained subqueries can be executed in isolated context, meanwhile correlated subqueries depend on the main/outer query.

HostForLIFEASP.NET SQL Server 2021 Hosting