SQL Nested Queries | SQL Sub Queries

In nested queries, a query is written inside a sql query, also called sql subquery.

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed.

Examples:

SELECT A.ID, 

 ( SELECT MAX(B.Income) FROM Table2 B  WHERE B.ID = A.ID ) AS MaxIncome

FROM Table1 A

SELECT Name FROM User 

  WHERE ID IN (SELECT ID FROM Topper WHERE Marks > 80)

 A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

A subquery nested in the outer SELECT statement has the following components:   

• A regular SELECT query including the regular select list components.
•  A regular FROM clause including one or more table or view names.
•  An optional WHERE clause.
•  An optional GROUP BY clause.
•  An optional HAVING clause.

The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query. Individual queries may not support nesting up to 32 levels. A subquery can appear anywhere an expression can be used, if it returns a single value.

If a table appears only in a subquery and not in the outer query, then columns from that table cannot be included in the output (the select list of the outer query).

Statements that include a subquery usually take one of these formats: 

• WHERE expression [NOT] IN (subquery)
• WHERE expression comparison_operator [ANY | ALL] (subquery)
• WHERE [NOT] EXISTS (subquery)

In some Transact-SQL statements, the subquery can be evaluated as if it were an independent query. Conceptually, the subquery results are substituted into the outer query (although this is not necessarily how Microsoft SQL Server actually processes Transact-SQL statements with subqueries).
 
There are three basic types of subqueries. Those that:

• Operate on lists introduced with IN, or those that a comparison operator modified by ANY or ALL.
• Are introduced with an unmodified comparison operator and must return a single value.
• Are existence tests introduced with EXISTS.

Reference:  Subquery Fundamentals