WITH

Function

WITH provides a way to write auxiliary statements for use in a larger query. These written statements are usually called common table expressions. The expressions can be considered as temporary views that exist only for a query. Common table expressions are often used to reuse query results and improve SQL readability.

Syntax Format

WITH <with_item_definition> [ , ... ]
SELECT ... FROM ...;
 
<with_item_definition>:
    with_item_name (column_name[, ...n]) AS ( <select_query> )

Syntax Example

The following example defines a common table expression resource_with_total and uses it in a GROUP BY query.

WITH resource_with_total AS (
    SELECT resource_id, name, type, count(*) AS total
FROM security_resource
group by resource_id, name, type
)
SELECT type, SUM(total)
FROM resource_with_total
GROUP BY type;