This example comes from Ikzik Ben-Gan’s book T-SQL Querying. Subqueries are used to nest queries. Subqueries is a feature where you want one query to operate on the result of another query. Subqueries are either self-contained or correlated. A self-contained subquery is independent of the outer query. A scalar subquery returns a single value. In the code listing below, the last subquery is a scalar subquery.

The last query in this listing is a self-contained subquery. The previous queries are just there to help us understand how the last one works. To see a database diagram of some of the tables in Itzik’s database, refer to the post called Itzik Ben-Gan’s Database.

 select custid, orderid, empid
 from Sales.Orders
 where custid = 2 
 order by orderid;
-----------------------------------------------
select custid, count(*) as NumberOfOrders
from Sales.Orders
group by custid;
----------------------------------------------
select custid, count(distinct empid) as NumOfDifferentEmps 
from Sales.Orders 
group by custid
------------------------------------------------
select custid
from Sales.Orders 
group by custid
having count(distinct empid) = 9;
------------------------------------------------------------------
-- Customers for whom ALL employees in the company took orders
select custid
from Sales.Orders 
group by custid
having count(distinct empid) = (select count(*) from HR.Employees);
-- ... assuming proper referretial integrity is enforced in the 
-- database preventing an order from having an employee DI that
-- doesn't appear in the HR.Employees table.

Below are the results in SSMS of the above queries. The second and third query each return 89 rows. Self-contained subqueries are easier to troubleshoot than correlated subqueries because you can run the subquery on its own simply by highlighting it with the mouse and Executing it independently of the outer query. The subquery is select count(*) from HR.Employees.

Products and Categories Example

How about a different example (use case)? When may you need a self-contained scalar subquery? Suppose you have a Categories table. You know the name of the category that is of interest to you, but you don’t know the Id of the category. You create a simple select query that returns the Id of the Category with the name “Shirts”. That returns a number. This is your subquery. Your outer query returns several records of Products that have an Id the represents “Shirts”.