Chapter 4: “Views, Materialized Views, and CTEs”

As you start to create more complex queries they could become harder to read, harder to debug, and harder to reuse. In order to write cleaner and more maintainable queries we can break them down into logical elements. SQL provides us with 3 tools for this: Views, Materialized Views, and Common Table Expressions (CTEs). These structures allow you to reuse important queries saving you time and energy while also making your queries a lot more readable.

Views:

A view is a virtual table that is based on a SELECT query, it does not store the data itself, but it runs the underlying query each time you use it. Here is an example of creating a view and using a view.

CREATE VIEW high_gpa_students AS
SELECT name, gpa
FROM students
WHERE gpa > 3.5;

Using the View:

SELECT * FROM high_gpa_students

As you can see from the view that we created and how we used it. You can treat a view as a table. The most common use cases for views are to simplify frequently used queries, to expose a clean interface to users, and to compute a result derived from a table in which the table is updated frequently and you need up to date results.

Common Table Expressions (CTEs):

A CTE is a temporary result set defined by using the WITH clause; it makes your query more readable by giving a name to an intermediate result. Once the CTE is defined the output is immediately used and discarded.

CTE Example:

WITH avg_grades AS (
  SELECT student_id, AVG(grade) AS avg_grade
  FROM grades
  GROUP BY student_id
)
SELECT s.name, a.avg_grade
FROM students s
JOIN avg_grades a ON s.student_id = a.student_id
WHERE a.avg_grade > 85;

CTEs are great for: Breaking queries into logical steps, Avoiding repeated subqueries, and when you only need to compute a result for a one-time use.

Recursive CTE Example (Advanced)

WITH RECURSIVE countdown(n) AS (
  SELECT 5
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1)
SELECT * FROM countdown;

Materialized VIew:

Materialized Views are precomputed query results, a materialized view stores the result of a query physically in the database. This makes querying much faster, but the data becomes stale unless refreshed.

Creating a Materialized View:

CREATE MATERIALIZED VIEW student_counts_by_major AS
SELECT major_id, COUNT(*) AS student_count
FROM students
GROUP BY major_id;

Using the Materialized View:

SELECT * FROM student_counts_by_major;

Refreshing the Materialized View:

REFRESH MATERIALIZED VIEW student_counts_by_major;

Use Cases of a Materialized View:

  • Large aggregate queries that don’t change often
  • Dashboards or reports
  • Caching expensive computations

The caveats of using a Materialized View:

  • Requires manual or scheduled refresh
  • Consumes storage space
  • Not supported by all DBMSs (PostgreSQL has strong support)
Next: Chapter 5 →