With Clause in SQL

With Clause in SQL

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name.

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Example 1:

// single subquery example 
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM regional_sales)
GROUP BY region, product;

Example 2:

// Multiple subquery with first subquery alias (regional_sales) is used in second subquery alias (top_regions)
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;

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download