I would like a simple subquery example using T-SQL 2008

Question

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

Answer 1

Maximilian Mayer believes that because of the link to the MS documentation , my claim that there is a difference between a subquery and subselect is incorrect. Honestly, I would consider the MSDN " Subquery basics are the best choice. Quote:

You distinguish between terms that actually mean the same thing.

O RLY?

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

... WHERE HAVING - - SELECT, INSERT, UPDATE DELETE. SELECT.

...

IE:

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

... , SELECT. - .

LEFT JOIN ANSI-89 - , . , .

, , - , .

/

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

... ( AKA).
" " - , , , - - SQL. , , , SELECT JOIN. , , . , /.

, . "", SELECT, , , (IE: subquery-subselect, subquery-subquery, ...). , : " ..."

"" , , , . , - , , - , , , .

Answer 2

, where:

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
        )

... 1000 .

Answer 3

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

Answer 4

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

, .

,

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

- , .