Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. The statement containing the subquery can also be called the outer query or the outer select. This makes the subquery the inner query or inner select. The inner query executes first so that the results can be passed on to the outer query to use.

The two tables we are using for this example are the same as the tables used in the post called SQL GROUP BY HAVING.

  SELECT subq.*
  FROM (
       SELECT * FROM dbo.Orders AS o
  ) subq  -- the subquery must have a name

Notice that the subquery is given a name. In this case, it is subq. The subquery is in the FROM statement. Below, the subquery is in the SELECT statement.

SELECT [OrderId]
      ,[EmpId]
      ,[Sales]
      ,(SELECT AVG(Sales) AS avg_sales
           FROM dbo.Orders) AS average_sales
FROM dbo.Orders 

It’s also common to see subqueries nested inside the WHERE statement.

SELECT [OrderId]
      ,[EmpId]
      ,[Sales]
FROM dbo.Orders 
WHERE Sales > (SELECT AVG(dbo.Orders.Sales) FROM dbo.Orders)

More Information

Here’s a post on Writing Subqueries in SQL by mode.com. Here’s another article about subqueries called SQL Subqueries.