Basic subquery knowledge

A subquery is a SELECT query that returns a single value and is nested in SELECT, INSERT, UPDATE, DELETE statements, or other subqueries. Subqueries can be used wherever expressions are allowed. In the following example, A subquery is used as a column expression named MaxUnitPrice In the SELECT statement.

SELECT Ord. OrderID, Ord. OrderDate,
(Select max (OrdDet. UnitPrice)
FROM Northwind. dbo. [Order Details] AS OrdDet
WHERE Ord. OrderID = OrdDet. OrderID) AS MaxUnitPrice
FROM Northwind. dbo. Orders AS Ord
 
A subquery is also called an internal query or an internal choice, and a statement containing a subquery is also called an external query or an external choice.

Many Transact-SQL statements that contain subqueries can be expressed by join. Other problems can only be raised by subqueries. In Transact-SQL, statements that include subqueries and statements that do not include subqueries but are semantically equivalent do not differ in performance. However, in some cases where you must check the existence, using a join produces better performance. Otherwise, to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. In these cases, the connection method will produce better results. The following example shows the SELECT subquery and SELECT join that return the same result set:

/** // * SELECT statement built using a subquery .*/
SELECT ProductName
FROM Northwind. dbo. Products
WHERE UnitPrice =
(SELECT UnitPrice
FROM Northwind. dbo. Products
WHERE ProductName = 'Sir round''s Scones ')
 
/** // * SELECT statement built using a join that returns
The same result set .*/
SELECT Prd1.ProductName
FROM Northwind. dbo. Products AS Prd1
JOIN Northwind. dbo. Products AS Prd2
ON (Prd1.UnitPrice = Prd2.UnitPrice)
WHERE Prd2.ProductName = 'Sir round''s Scones'
 
Subqueries nested in external SELECT statements include the following components:

Standard SELECT query that contains the standard selection list component.

Standard FROM clause that contains one or more tables or view names.

Optional WHERE clause.

Optional group by clause.

Optional HAVING clause.
The SELECT query of A subquery is always enclosed in parentheses. And cannot include the COMPUTE or for browse clause. If the TOP clause is specified at the same time, it may only include the order by clause.

Subqueries can be nested in the WHERE or HAVING clause of external SELECT, INSERT, UPDATE, or DELETE statements, or in other subqueries. Although the nesting restriction varies depending on the complexity of other expressions in the available memory and query, it is possible to nest the expressions to 32 layers. Individual queries may not support 32-layer nesting. You can use subqueries wherever expressions are available, as long as they return a single value.

If a table appears only in the subquery but not in the external query, the columns in the table cannot be included in the output (select the external query list ).

Statements that include subqueries generally use one of the following formats:

WHERE expression [NOT] IN (subquery)

WHERE expression comparison_operator [ANY | ALL] (subquery)

WHERE [NOT] EXISTS (subquery)
In some Transact-SQL statements, subqueries can be evaluated just like an independent query. In terms of concept, the subquery results will be substituted into external queries (although you do not have to know how Microsoft SQL Server actually processes Transact-SQL statements through subqueries ).

There are three basic subqueries. They are:

Operate on the list introduced by IN or the list of comparison operators modified by ANY or ALL.

It is introduced through a non-modified comparison operator and must return a single value.

The existence test introduced by EXISTS.
Use the FROM clause
The FROM clause is required in each SELCET statement that retrieves data FROM a table or view. You can use the FROM clause:

Lists the tables and views of the columns referenced in the select list and WHERE clause. The AS clause can be used to specify aliases for the names of tables and views.

Join type. These types are limited by the join conditions specified in the ON clause.
The FROM clause is a list of table names, view names, and JOIN clauses separated by commas.

With the extended functions, you can specify objects other than tables or views in the FROM clause. These objects return a result set, which is the row set in ole db terminology. The result set constitutes a virtual table. Then the SELECT statement operates these result sets just like the operation table.

The FROM clause can specify:

One or more tables or views. Example: SELECT *
FROM Shippers
Join two tables or views: SELECT Cst. mermerid, Cst. CompanyName, Cst. ContactName,
Ord. ShippedDate, Ord. Freight
FROM Northwind. dbo. Orders AS Ord
JOIN
Northwind. dbo. Customers AS Cst
ON (Cst. CustomerID = Ord. CustomerID)
One or more derived tables are SELECT statements in the FROM clause, which are referenced by aliases or user-specified names. The result set of the SELECT statement in the FROM clause constitutes the table used by the outer SELECT statement. For example, the following SELECT statement uses a derived table to find out which Bookstore has all types of books in the pubs database: select st. stor_id, ST. stor_name
FROM stores as st,
(SELECT stor_id, COUNT (DISTINCT title_id) AS title_count
FROM sales
Group by stor_id
) AS SA
Where st. stor_id = SA. stor_id
And sa. title_count = (select count (*) FROM titles) note that as sa is mandatory AND must be an alias for the SELECT statement in the FROM clause.
Use sp_addmediaserver to define one or more tables or views in the linked server. The linked server can be any ole db data source.

Use the ole db row set returned by the OPENROWSET or OPENQUERY function.