SQL Subqueries
INSERT INTO TableA (PosID, PosValue, DataDate) SELECT PosID, PosValue, DataDate FROM TableB WHERE DataDate = '2006-10-6' AND PosID = 'TAEE1'
^ 把Values (A,B,C)部分替换成一个SELECT A,B,C语句便可。
UPDATE TableA SET PosValue = (SELECT PosValue FROM TableB WHERE
DataDate = '2006-10-6' AND PosID = 'TAEE1' ) WHERE PosID = 'TAEE1' AND DataDate = '2006-10-6'
^
SELECT OUID, (SELECT ID, OUName FROM Accounts_OU WHERE ID = A.OUID ORDER BY ID) AS OUName FROM BizMgtStationOrder A WHERE PipeLineID = " & XXX & " ORDER BY OUID"
d Subquery Fundamentals
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a 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 inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. This is an example showing both a subquery SELECT and a join SELECT 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 Rodney''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 Rodney''s Scones'
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.
