Correlated Sub-Query
Very efficient as opposed to usually selecting into a temp table the reselecting desired result set from there.
Looks like a sub query but basically the nested query references the outer query by way of the outer being aliased.
The aliasing *has* to happen.
n.b. a normal subquery is only very efficient if the tables have < 10,000 rows.
Derived tables
Subquery in parentesis that is then aliased
OUTPUT
2005 extension to DML - allows you to see changes a statement makes (without using triggers) into a table var.
UPDATE X SET COL1 = COL1 + COL2
OUTPUT DELETED.X + INSERTED.COL1
INTO @CHANGES
SELECT * FROM CHANGES