Derived tables   :-

  • This is introduced from SQL Server 2005.
  • Sub queries follows from clause are called “Derived tables”.

Syntax :

Select * from (select statement) as alias.

  • Use derived tables to simplify the complex processing.
  • To use result of one query in another query.

Example  :-

Select * from (select empno,ename,sal*12 annsal from emp) as e where annsal >2000.

Display top maximum 3 salaries in emp table :-

Select distinct sal from (select ename,sal, dense_rank ( ) over (order by sal desc) rnk from emp) as e where rnk <=3

{to remove this one, this is top min 3 sal}

Display department wise top maximum 3 salaries in emp table :-

Select distinct deptno,sal from (select ename,sal,deptno,dense_rnk ( ) over (partition by dept no order by sal desc rnk from emp) as e Where rnk c=3 order by deptno,sal desc

sales 1, sales 2, cust

Select * from (select * from sales 1 union all select * from sales 2) as s, cust c where s.cusid=c.cusid