I was recently attempting to explain to someone the difference between derived tables and subqueries. I didn't do a great job and came to the conclusion that I wasn't completely clear on the terminology. I don't like not knowing things so I did a little research and this is what I found . . .
A derived table is not what I thought it was. I always referred to a derived table as being a query in a FROM clause. For example:
id AS EmployeeId,
name AS EmployeeFullName,
COUNT(*) AS PaychecksIssuedCount
employee_type_cd = 101) emp
INNER JOIN paycheck ps
on emp.id = ps.emp_id
Apparently this is actually called a subquery and can be referred to as a subquery in a FROM clause. It's a bit of symantics but a derived table is actually the result of a table subquery.
For some reason people that use SQL Server (like me) tend to refer to this as a derived table. I guess I'll have to stop doing that now.
As for correlated subqueries, these can be found in the list of projected columns as well as the WHERE and HAVING clauses. For example:
emp_avg_years.business_unit = emp.business_unit)
As you can see, the subquery has a WHERE clause that refers (correlates) to the outer/parent query.
So there you have it. Stop saying derived tables! Most likely you're misusing it the way I have ever since I learned about the concept (of subqueries - not derived tables ;)). Or maybe you're not as anal as I am. However, if you've read this far I'd say you are.