Derived Table

Sub Query


derived tables are used in the FROM clause


sub queries are used in the WHERE clause, but can also

be used to select from one table and insert into

another as we showed above


A common table expression (CTE) is a similar concept to a derived table in the sense that it’s

a named table expression that is visible only to the statement that defines it. Like a query

against a derived table, a query against a CTE involves three main parts:

■ The inner query

■ The name you assign to the query and its columns

■ The outer query

select employee_name

from employee

where employee_salary >

 (select avg(employee_salary)

        from employee)


select max(age)

from (

select age from table

) as Age  



With C As


SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE country = 'USA'


Select empid, firstname, lastname, country from c