Derived Table, Subquery, Correlated Subquery, etc.

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:

    SELECT
        id AS EmployeeId,
        name AS EmployeeFullName,
        COUNT(*) AS PaychecksIssuedCount
    FROM
        (SELECT
             id,
             name
          FROM
              employee
          WHERE
               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:

SELECT
    id,
    name
FROM
    employee emp
WHERE
    years_employed =  
        (SELECT
            AVG(years_employed)
         FROM
            employee emp_avg_years
         WHERE
            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.