You may know that you can use subqueries in the WHERE clause, HAVING clause, FROM clause, and SELECT clause.

Before you read examples…

Listen to my podcast, which tells about some important interview questions on subqueries.

Example to use a subquery in the WHERE clause

SELECT
     SalesOrderID,
     RevisionNumber,
      OrderDate
FROM Sales.SalesOrderHeader
WHERE EXISTS (SELECT
       1
       FROM sales.SalesPerson
       WHERE SalesYTD > 3000000
       AND SalesOrderHeader.SalesPersonID
                = Sales.SalesPerson.BusinessEntityID);

Subquery in HAVING clause example

SELECT   JobTitle,
         AVG(VacationHours) AS AverageVacationHours
FROM     HumanResources.Employee
GROUP BY JobTitle
HAVING   AVG(VacationHours) > (SELECT AVG(VacationHours)
                               FROM   HumanResources.Employee);

Sub query in FROM clause

SELECT
 x.ProductID,
 y.ProductName,
 x.max_unit_price
FROM (SELECT
     ProductID,
     MAX(UnitPrice) AS max_unit_price
     FROM order_details
     GROUP BY ProductID) AS x
     INNER JOIN products AS y
     ON x.ProductID = y.ProductID;

Sub query in SELECT clause

SELECT DISTINCT
 title_id,
 (SELECT
  au_id
  FROM titleauthors
  WHERE au_ord = 1
  AND title_id = t.title_id)
  AS first_author,
 (SELECT
  au_id
  FROM titleauthors
  WHERE au_ord = 2
  AND title_id = t.title_id)
  AS second_authors,
 (SELECT
   au_id
   FROM titleauthors
   WHERE au_ord = 3
   AND title_id = t.title_id)
   AS third_author
   FROM titleauthors t;