SQL: WITH clause (Common Table Expressions)

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. Not every database supports WITH clause. However, PostgreSQL dose support it. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it is an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

WITH clause provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single sub-query alias.

WITH <alias_name> AS (sql_subquery_statement)SELECT column_list FROM <alias_name> [, tablename][WHERE <join_condition>]

When using multiple subquery aliases, the sysntax is as follows.

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )SELECT <column_list>FROM <alias_name_A>, <alias_name_B> [, tablename][WHERE <join_condition>]

The only advantage of using a CTE over a sub select is that you can actually name the sub query.

Following is an example.

WITH regional_sales AS (

      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region), 
top_regions AS (
      SELECT region
      FROM regional_sales
      WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))

SELECT region,
      product,
      SUM(quantity) AS product_units,
      SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH Clause Is Most Useful For Recursion

When WITH clause is used to recursively perform a task, it can accomplish things not otherwise possible in standard SQL. In this case, a WITH query can refer to its own output. An example is this query to sum the integers from 1 through 100 shown below.

WITH  numbs(n) AS ( //recursively put all qualified n into numbs
      SELECT 1 from dual    //initialize the first value
      UNION ALL
      SELECT n+1 FROM numbs  //select from itself
      WHERE n < 100            //condition to stop
)
SELECT sum(n) FROM numbs;

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.