AP 20 - Avoid unused Common Table Expressions (CTE)

Severity

Type

Problem

One of the Common Table Expressions (CTEs) that the query defines is not used anywhere else in the query.

Example

The CTE sum_students has been defined but is not referenced in other parts of the query. Only avg_students has been referenced in the query

WITH avg_students AS(
    SELECT district_id
          ,AVG(fee) AS average_students
      FROM schools
     GROUP BY district_id)
 ,sum_students AS(
    SELECT district_id
          ,SUM(fee) AS sum_students
      FROM schools
     GROUP BY district_id)
SELECT s.school_name
      ,s.district_id
      ,avg.average_students
  FROM schools s
  JOIN avg_students avgs
    ON s.district_id=avgs.district_id

There are two issues:

• Including an unused CTE makes the query more complex and less readable. It may confuse other SQL developers
• It may cause a performance degradation with some databases. Some databases materialise CTEs by default. Other databases inline the CTE. When the database materialises the CTE it will set down the resultset physically or in memory. For some databases you can specify if you want to materialise or inline a CTE. Materialising a resultset allows the database to reuse the result multiple times. Some databases even materialise the resultset of unreferenced CTEs. This requires extra work for no good reason.

Solution

Remove CTEs that aren’t being used, or at least comment them out for future use.

Legitimate use of the anti pattern

None.