The SQL WITH clause allows you to give a sub-query block a name which can be referenced in several places within the main SQL query.

 Syntax for the SQL WITH clause.

This syntax is for SQL WITH clause using a single sub-query alias.

     WITH <alias_name> AS (sql_sub-query_statement)  
      SELECT column_list FROM <alias_name> [table name]        [WHERE <join_condition>]

 

This syntax is for SQL WITH clause use multiple sub-query aliases :

 

WITH <alias_name_A>  AS (sql_sub-query_statement)  
<alias_name_B> AS (sql_sub-query_statement_from_alias_name_A 
Or sql_sub-query_statement) 
SELECT <column_list> 
FROM <alias_name_A >,< alias_name_B >, [tablenames]
[WHERE < join_condition>]

 

Example WITH clause using a single sub-query alias:

 

WITH alias AS (SELECT * FROM Student)SELECT * FROM alias

 

Example WITH clause multiple sub-query aliases:

 

WITH std AS(SELECT * FROM Student),
      std1 AS( select * from Student )
SELECT std.id,std.Name,std.Mobile,std.address1,std.age FROM std,std1 



Thanks.

0 comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

you may also like