SQL - With Clause - Explained with examples

In this blog, we are going to see SQL With Clause

Syntax of the SQL WITH clause using a single sub-query alias

WITH <alias_name> AS (sql_subquery_statement)

SELECT column_list FROM <alias_name>[,table_name]

[WHERE <join_condition>]

Example:

With
Empl_Name	--alias_name
as
(
select
'1234' Emplid	--Column 1
,
'Test1' Name	--Column2
from
Dual	--Table Name
)
Select
*	-- all columns
from
Empl_Name	-- alias_name

Result:

Emplid	Name
1234	Test1

Syntax of the SQL WITH clause using a multiple sub-query alias

WITH <alias_name_A> AS (sql_subquery_statement),

<alias_name_B> AS(sql_subquery_statement_from_alias_name_A

or sql_subquery_statement )

SELECT <column_list>

FROM <alias_name_A>, <alias_name_B> [,table_names]

[WHERE <join_condition>]

Example: 

With
Empl_Name	--alias_name_A
as
(
select
'1234' Emplid	--Column1
,
'Test1' Name	--Column2
from
Dual	--Table Name
)
,
Empl_Position	--alias_name_B
as
(
select
'1234' Emplid	--Column1
,
'Position1' Position	--Column2
from
Dual	--Table Name
)
Select
Empl_Name.Emplid	--alias_name_A.Column1
,
Empl_Name.Name	--alias_name_A.Column2
,
Empl_Position.Position	--alias_name_B.Column2
from
Empl_Name	-- alias_name_A
,
Empl_Position	-- alias_name_B
where
Empl_Name.Emplid	--alias_name_A.Column1
=
Empl_Position.Emplid	--alias_name_B.Column1

Result:  

Emplid	Name	Position
1234	Test1	Position1

I hope this blog explained SQL With Clause. If you have any questions, please post it in the comments section