The easiest and simplest way to explain what a subquery is to say that it's a query within a query. For example if you want to get the employee that belongs to specific territory in the Northwind database without a join, you would have to use a subquery. Like the following subquery.

SELECT EmployeeID, (FirstName + ' ' + LastName) AS Name
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID 
       FROM EmployeeTerritories 
       WHERE TerritoryID=01581) 

Things You Should Know About Subqueries:

• They are not the most efficient performance wise
• You can only retrieve a single column in the subquery, retrieving multiple columns will throw an error

Another way to use subqueries is to use it with Aggregate functions like the query below, which gets the average price for the category with ID value of 1:

SELECT CategoryName,
 (SELECT AVG(UnitPrice) 
 FROM Products WHERE CategoryID = 1) AS AvgPrice
FROM Categories
WHERE CategoryID = 1