SQL Server Subquery

Subquery: A subquery—also referred to as an inner query or inner select—is a SELECT statement embedded within a data manipulation language (DML) statement or nested within another subquery. You can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed.

A DML statement that includes a subquery is referred to as the outer query. The following guidelines provide details about how to implement subqueries in your outer queries or in other subqueries:

·         You must enclose a subquery in parenthesis.

·         A subquery must include a SELECT clause and a FROM clause.

·         A subquery can include optional WHERE, GROUP BY, and HAVING clauses.

·         A subquery cannot include COMPUTE or FOR BROWSE clauses.

·         You can include an ORDER BY clause only when a TOP clause is included.

·         You can nest subqueries up to 32 levels.

A subquery is a SQL query nested inside a larger query.

• A subquery may occur in :

A SELECT clause

A FROM clause

A WHERE clause

• The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
• A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
• You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL.
• A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
• The inner query executes first before its parent query so that the results of inner query can be passed to the outer query.
• When subqueries are used in a SELECT statement they can only return one value. This should make sense, simply selecting a column returns one value for a row, and we need to follow the same pattern.
• In general, the subquery is run only once for the entire query, and its result reused. This is because, the query result does not vary for each row returned.
• It is important to use aliases for the column names to improve readability.

Syntax :

SELECT select_list

FROM Table1

WHERE expr Operator

              (SELECT select_list

                FROM Table1)

• The subquery (inner query) executes once before the main query (outer query) executes.
• The main query (outer query) use the subquery result.

Adding Subqueries to the SELECT Clause :

You can add a subquery to a SELECT clause as a column expression in the SELECT list. The subquery must return a scalar (single) value for each row returned by the outer query.

For example, in the following SELECT statement, I use a subquery to define the TotalQuantity column:

SELECT

  SalesOrderNumber,

  SubTotal,

  OrderDate,

  (

    SELECT SUM(OrderQty)

    FROM Sales.SalesOrderDetail

    WHERE SalesOrderID = 2356

  ) AS TotalQuantity

FROM

  Sales.SalesOrderHeader

WHERE

  SalesOrderID = 2356;

Notice I’ve inserted the subquery as the fourth column expression in the SELECT list and named the column TotalQuantity. The subquery itself is enclosed in parentheses and made up of a single SELECT statement. The statement retrieves the total number of items sold for sales order 43659. Because there are multiple line items in this order, I used the SUM aggregate function to add the numbers together and return a single value. The following table shows the result set returned by the outer SELECT statement.

SalesOrderNumber	SubTotal	OrderDate	TotalQuantity
SO43659	24643.9362	2001-07-01 00:00:00.000	26