rbarryyoung (3/30/2008)
It is important to realize that there are several types of Subquery:1. Value Subqueries: These just return a single value per row and they look like this:
(Select col1 From ... )If they return no value, you get a NULL, if they return multiple values, you will fail with an error. They are most often used to assign a value to an output column, or to compare to a value in your where clause.2. IN subqueries: These return any number of rows, each with a single column and they look like this:
WHERE value IN(Select col1 From ... )They are typically used in WHERE clauses to test if a particular value is in the list of values returned.3. EXISTS subqueries: These return any number of rows with any number of columns and they look like this:
WHERE EXISTS(Select * From ... )They are typically used in WHERE clauses to test if corresponding rows in another table exist. Because the column values returned are not actually used, typicaly you use a "*" instead of bothering to name any columns.
4. Derived tables: These are used in the From clause, may return any number of columns and any number of rows. Once aliased, they can be joined to other tables and treated just like a table in the other clauses of the query.
Eg.
SELECT TheTable.Col1, TheDerivedTable.Col2, TheDerivedTable.SumOfCol2, TheTable.Col4
FROM TheTable
INNER JOIN
(SELECT Col1, Col2, SUM(Col3) AS SumOfCol3
FROM SomeOtherTable
GROUP BY Col1, Col2) TheDerivedTable
ON TheTable.Col1 = TheDerivedTable.Col1
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability