SQL query WITH clause | Database

• The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query.
• The usage of WITH clause is very similar to creating tables. When you create a table, you give it a name. Similarly, when you use the WITH caluse, you also give it a name  and this name essentially acts like a table name in the main SQL statement.
• WITH clasue dose not create a table or view, the object associated with the WITH statement disappears after the main SQL statement is executed, and there is nothing to clean up.

Syntax for single sub-query alias:

WITH <alias_name> AS (sql_sub-query_statement)

Sysntax for multiple sub query :

WITH <alias_name_1> AS (sql_sub-query_statement_1), <alias_name_2> AS (sql_sub-query_statement_2),.....

Example:

Suppose we want to list all the stores that have more sales than the average.

With t1 as 

(Select avg(sales) avg_sales from store_sales)

Select a1.* from store_sales a1, t1

Where a1.sales > t1.avg_sales;

Input:

Store name sales

A 15000

B 2500

C 3000

D 7000

Output:

Store_Name Sales

A 15000

D 7000