I would like a simple example of a sub-query using T-SQL 2008

Question

Can anyone give me a good example of a subquery using TSQL 2008?

Answer 1

SELECT
    *,
    (SELECT TOP 1 SomeColumn FROM dbo.SomeOtherTable)
FROM
    dbo.MyTable

Answer 2

SELECT     a.*, b.*
FROM       TableA AS a
INNER JOIN
(
    SELECT *
    FROM   TableB
) as b
ON        a.id = b.id

Thats a normal subquery, running once for the whole result set.

On the other hand

SELECT     a.*, (SELECT b.somecolumn FROM TableB AS b WHERE b.id = a.id)
FROM       TableA AS a

is a correlated subquery, running once for every row in the result set.

Answer 3

For variety's sake, here's one in the where clause:

select 
    a.firstname,
    a.lastname
from
    employee a
where
    a.companyid in (
        select top 10
            c.companyid
        from
            company c
        where
            c.num_employees > 1000
        )

...returns all employees in the top ten companies with over 1000 employees.

Answer 4

Maximilian Mayer believes that, due to referencing MS documentation, my assertion that there is a difference between a subquery and a subSelect is incorrect. Frankly, I'd consider MSDN's "Subquery Fundamentals" a better choice. Quote:

You are making distinctions between terms that actually mean the same.

O RLY?

A subQUERY...

IE:

WHERE id IN (SELECT n.id FROM TABLE n)
   OR id = (SELECT MAX(m.id) FROM TABLE m) 
   OR EXISTS(SELECT 1/0 FROM TABLE) --won't return a math error for division by zero

...affects the WHERE or HAVING clauses -- the filteration of data -- for a SELECT, INSERT, UPDATE or DELETE statement. The value from a subquery is never directly visible in the SELECT clause.

A subSELECT...

IE:

SELECT t.column,
       (SELECT x.col FROM TABLE x) AS col2
  FROM TABLE t

...does not affect the filteration of data in the main query, and the value is exposed directly in the SELECT clause. But it's only one value - you can't return two or more columns into a single column in the outer query.

A subselect is a consistent means of performing a LEFT JOIN in ANSI-89 join syntax - if there is no supporting row, the column will be null. Additionally, a non-correlated subselect will return the same value for every row of the main query.

Correlation

If a subquery or subselect is correlated, that query runs once for every record of the main query returned -- which doesn't scale well as the number of rows in the result set increases.

Derived Table/Inline View

IE:

SELECT x.*,
       y.max_date,
       y.num
  FROM TABLE x
  JOIN (SELECT t.id,
               t.num,
               MAX(t.date) AS max_date
          FROM TABLE t
      GROUP BY t.id, t.num) y ON y.id = x.id

...is a JOIN to a derived table (AKA inline view).
"Inline view" is a better term, because that is all that happens when you reference a non-materialized view -- a view is just a prepared SQL statement. There's no performance or efficiency difference if you create a view with a query like the one in the example, and reference the view name in place of the SELECT statement within the brackets of the JOIN. The example has the same information as a correlated subquery, but the performance benefit of using a join and none of the subquery detriments. And you can return more than one column, because it is a view/derived table.

Conclusion

It should be obvious why I and others make distinctions. The concept of relying on the word "subquery" to categorize any SELECT statement that isn't the main clause is fatality flawed, because it's also a specific case under a categorization of the same word (IE: subquery-subselect, subquery-subquery, subquery-join...). Now think of helping someone who says "I've got a problem with a subquery..."

Maximilian Mayer's idea of "official" documentation was written by technical writers, who often have no experience in the subject and are only summarizing what they've been told to from knowledgeable people who have simplified things. Ultimately, it's just text on a page or screen -- like what you're reading now -- and the decision is up to you if the details I've laid out make sense to you.