SQL WITH CLAUSE - The Coding Shala

Home >> Learn SQL >> SQL WITH Clause

SQL WITH CLAUSE

The SQL WITH Clause is used to provide a sub-query block which can be referenced in several places within the main SQL query. It was introduced by ORACLE in Oracle 9i release 2 databases. Alternate of 'with' clause is nested sub-queries but they are more complex to read and debug. The SQL WITH Clause is not supported by all the databases.
SQL WITH CLAUSE - The Coding Shala

SQL WITH CLAUSE SYNTAX

The following is basic SQL WITH Clause syntax - 

WITH <alias_name> AS (sub-query_statement)   
 SELECT columns_name FROM table   
 [WHERE <join_condition>]  

SQL WITH CLAUSE EXAMPLE

The following SQL query will return all those records that have Salary is more than average Salary - 

WITH tempTable(avgsal) as  
      (SELECT avg(Salary)  
      from emp),   
     SELECT * from emp   
     WHERE Table.Salary > tempTable.avgsal;



Other Posts You May Like
Please leave a comment below if you like this post or found some error, it will help me to improve my content.

Comments

Popular Posts from this Blog

Add two numbers in Scala - The Coding Shala

Shell Script to Create a Simple Calculator - The Coding Shala

Goal Parser Interpretation LeetCode Solution - The Coding Shala

Maximum Population Year LeetCode Solution - The Coding Shala

Shell Script to find sum, product and average of given numbers - The Coding Shala