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.Name,std.Mobile,std.address1,std.age FROM std,std1 



Leave a comment

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

Welcome to SQL Tutorials Point

Don't have an account? Click here to Create a new account, it takes less than a minute.