You may know that sub query you can use in WHERE Clause, HAVING clause, FROM clause and Select clause.

Before you read examples…

Listen my podcast, which tells about some important interview questions on Sub-queries.

Example to use Sub query in 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);

Sub Query 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;

Also read: Join Vs SUB-query in SQL