WITH Clause

The WITH expression was introduced in the standard SQL language to simplify long and complex queries, especially those with JOINs and subqueries. Often referred to as CTE (Common Table Expression) or subquery refactoring, the WITH clause defines a temporary set of data whose result can be used in other queries.

The best way to learn to work with the WITH expression in SQL is through practice. The WITH clause is considered temporary because its result is not stored anywhere inside the database but is used as a temporary view that exists only during the execution of the query. It means that it is only available when the database is queried using SELECT, INSERT, UPDATE, DELETE, or MERGE statements. The WITH condition works only with the query to which it belongs, improving the query structure without increasing the number of additional aliases. The WITH expression was introduced in 1999 and is available today in most relational databases. With this expression, we can:

  1. Refer to temporary tables multiple times within a single query.
  2. Perform multi-level aggregations, such as finding averages from extremes.
  3. Perform identical calculations in the context of a larger query.
  4. Use it as an alternative to creating a view in the database.

So, when do we really need the WITH clause? There are several cases where its use is recommended. Most of them involve improving query creation and simplifying change management. The key advantages of using WITH include:

  1. Increased code readability: It allows organizing code in a way that makes it easy to understand its dependencies with minimal effort. The WITH clause enables breaking down a larger query into several smaller ones for easier comprehension of individual calculations.
  2. Simplified change management: The WITH condition affects both clarity and change management. As our queries and databases grow over time, it leads to the need for debugging and optimizing the database. Code that is easier to read is also easier to maintain.
  3. Alternative to views: The WITH clause can replace views. This can be useful when there are no system settings to create a view, or if you don’t want to create a view that will only be used for a single query.
  4. Hierarchical structure processing: This is one of the more advanced applications, allowing you to refer to complex queries. This allows the creation of hierarchical models.

Pattern Overview

So how do you use the WITH clause? Let’s discuss this with the following example:

WITH TempQuery AS ( SELECT col1, col2, col3, SUM(col4) AS Attr1 FROM tab1 GROUP BY col1, col2, col3 ) SELECT col1, col2, col3, Attr1 FROM TempQuery WHERE Attr1 > 100;

The above query creates a temporary data set called TempQuery, which contains the sum of values grouped by three other columns. Then, in the SELECT query, we refer to this query, filtering the value of attribute Attr1 for values greater than 100. Of course, this is just a simple example, but don’t forget that temporary queries can also be combined with other functions.

WITH ActualQuery AS ( SELECT col1, col2, col3, SUM(col4) AS attr1 FROM ActualTab GROUP BY col1, col2, col3 ), ArchivedQuery AS ( SELECT col1, col2, col3, SUM(col4) AS attr1 FROM ArchivedTab GROUP BY col1, col2, col3 ) SELECT * FROM ActualQuery UNION SELECT * FROM ArchivedQuery;

The above query combines two tables with the same structure.

Summary

I introduced the WITH clause in the article, discussed its application, and then explained the benefits of using this expression, providing practical examples of its use.

Interesting article? Share further!

Leave A Comment