What is Sub Query,Derived Table,Inline View and Common Table Expressions in SQL Server

How to define Sub Query,Derived Table,Inline View and Common Table Expressions(CTEs) in SQL Server
Sub Query:
A SubQuery is a T-SQL query that is nested in side the another Query. Sub Queries are used to break a query into a series of logical steps.You can use the Joins inside the sub queries to get the data from multiple tables for your analysis.

In Sub Queries, the inner SubQuery will execute first, next based on the SubQuery result the outer Query will be executed.

Eg :
SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal FROM 
(
   SELECT Emp_Id,Emp_Name,D.Dept_Name,Job_Id,Basic_Sal 
   From EMP E LEFT Join DEPT D 
   ON E.Dept_Id= D.Dept_Id 
  ) S1
INNER JOIN JobTitles J ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL

Nested Sub Query :
If  a Multiple SubQueries are nested in an Outer Query then its referred as Nested SubQuery.

Eg:
SELECT * From(
   SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal, Row_Number() OVER(order by Basic_Sal Desc) as SAL_Rnk
FROM (
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E LEFT JOIN DEPT D        ON E.Dept_Id= D.Dept_Id 
            ) S1 INNER JOIN JobTitles J 
              ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL
) S2 WHERE S2.SAL_Rnk=1

Correlated Sub Query :
In Correlated Sub Queries, first outer Query will be executed,then inner SubQuery will execute based on the outer Query input then it will pass back the results to outer Query, the outer Query will be execute again.
Eg :
SELECT E1.Emp_Id,E1.Emp_Name,E1.Joining_date from EMP E1 
WHERE E1.Joining_date=(SELECT Min(E2.Joining_date) as EarlyJoin from EMP E2
WHERE E2.Emp_Id=E1.Emp_Id) 
ORDER by Emp_Id

Derived Table : 
A derived table is made up of the columns and rows of result set from a Query.It is the Alias name we usually give to a Query result set.
Eg:
SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal FROM 
(SELECT Emp_Id,Emp_Name,D.Dept_Name,Job_Id,Basic_Sal from EMP E 
LEFT Join DEPT D ON E.Dept_Id= D.Dept_Id ) S1 --Derived Table
INNER JOIN JobTitles J ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL

Here in the above query the alias name 'S1' is nothing but a Derived Table.

Inline View :
An inline view is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a view is because it essentially functions as a view as far as the entire query is concerned.
Please remember that an inline view only exists in the query in which it is created , that is why they are called inline views. So, inline views are not actually part of the database schema because they are not real views.
Inline Views and Derived Tables are Same.

Eg:
Select Max(Basic_Sal) from 
( 
--This Part of a Query is an Inline Veiw
Select Basic_Sal from Emp 
)

Common Table Expressions:
The CTE works in the similar way Derived Table, and CTEs are declared upfront rather than inline in the Query, and end with a semicolon. We use the keyword 'WITH' to define the CTEs.
Example - Simple CTE:
WITH MyCTE AS
(
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E 
LEFT Join 
DEPT D ON E.Dept_Id= D.Dept_Id 
)
Select* FROM MyCTE WHERE Dept_Name IS NULL  
;
GO

Example - Multiple CTE:
WITH MyCTE1 AS
(
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E 
LEFT Join 
DEPT D ON E.Dept_Id= D.Dept_Id ),
MyCTE2 AS (
Select Emp_id,Emp_Name,Dept_Name,Basic_Sal,J.Job_Id,J.Job_Title,
Rank() OVER(order by Basic_Sal Desc)as SAL_Rnk FROM MyCTE1 M
INNER JOIN
JobTitles J ON M.Job_Id = J.Job_Id WHERE M.Dept_Name IS NULL  
)
Select*from MyCTE2 where MyCTE2.SAL_Rnk=1
;
GO

-------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------