In this blog, we are going to see SQL With Clause
Example:
Result:
Syntax of the SQL WITH clause using a multiple sub-query alias
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
|
Emplid
|
Name
|
1234
|
Test1
|
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
Useful content.. how to fetch count of emplid, sum of hours using group by clause using with clause function..
ReplyDelete