SQL WITH Clause – Tutorial and Examples

Board Infinity -

Introduction

WITH Clause in SQL was introduced by Oracle in the Oracle 9i release. It was introduced to simplify complex queries which include joins, and nested queries. Sub queries. WITH clause in SQL is used in hand with CTE( Common Table Expressions) and is also called subquery refactoring. This WITH clause in SQL is used for defining temporary data which can be accessed by subsequent queries.

This is temporary data set because it is not stored anywhere in the database schema. It basically acts as a temporary view that is available only during the execution and helps during the execution of UPDATE, DELETE, SELECT, and INSERT statements.  The main advantages of WITH clause in SQL are as follows:

  • The main reason why WITH clause in SQL is helpful is, as it helps in organizing and simplifying long and complex hierarchical queries by breaking them down into smaller, and simpler queries.
  • It helps in referencing the temporary table any number of times within the query in CTE. It is also used as an alternative for creating VIEW in SQL. This WITH clause is not supported by all database systems.
  • With the help of WITH clause in SQL  improves code readability and code maintenance is comparatively easier.
  • With the help of WITH Clause instead of creating a view in every single query, we can use WITH clause as it is also a temporary table that can be assessed but not stored in the database.

The syntax to be followed for WITH clause in SQL is as follows:

Firstly initialize the temporary table name as TempTable which contains only one Attribute which is Attribute1, which is the maximum of all values present in Attr_1 of the table Table which is given. Using the query below we are returning the values of Attr_1  from the Table which is smaller than the (Maximum value of Attr_1)  Attribute1  in the given table.  We can say that when a query is executed using WITH clause in SQL, firstly the query within the clause is executed and the output is stored in the Attributes mentioned in the temporary table using this result, the given query is executed.

Examples: Below examples clearly explain the WITH clause in SQL in detail:

From the given OrdersDetails table, the query is to return the order’s details whose number of orders is always greater than the average number of orders of all the Orders using WITH clause. The given OrderDetails table is as follows:

The query is written as follows:

We can observe that the average value of all the number of orders by each location is stored in the first attribute of the temporary table i.e, the average is 8 and is stored in avgVal using this result, Using this result,  the main query is executed and the output is returned as follows:

Conclusion:

WITH clause in SQL is used in hand with CTE( Common Table Expressions) and is also called subquery refactoring. This WITH clause in SQL is used for defining temporary data which can be accessed by subsequent queries. This is considered as the temporary data set. WITH Clause in SQL is helpful for simplifying complex queries.