Views and recursive CTEs — Advanced SQL: windows, views and CASE

Views: named queries

A view (VIEW) is a saved query that you give a name. It does not store data: every time you use it, its SELECT runs underneath. It serves to avoid repeating complex queries and to give clear names to the team.

CREATE VIEW expensive_products AS
SELECT name, category, price
FROM products
WHERE price >= 200;

From then on, you use it as if it were a table:

SELECT * FROM expensive_products ORDER BY price DESC;
SELECT category, COUNT(*) FROM expensive_products GROUP BY category;

A view always reflects the current data of the base tables: if the products change, the view shows them instantly (there is no copy to update).

CTEs: the WITH

A CTE (Common Table Expression) is a named temporary query that only lives during the query in which it is declared, with WITH:

WITH expensive AS (
  SELECT * FROM products WHERE price >= 200
)
SELECT category, AVG(price) FROM expensive GROUP BY category;

Compared with a view, the CTE is local (it is not saved) and makes the query more readable by splitting it into steps.

Recursive CTEs (intro)

With WITH RECURSIVE a CTE can refer to itself, ideal for generating series or traversing hierarchies. It has two parts joined by UNION ALL:

WITH RECURSIVE numbers(n) AS (
  SELECT 1                        -- base case: the starting point
  UNION ALL
  SELECT n + 1 FROM numbers       -- recursive step: uses the previous row
  WHERE n < 5                     -- stop condition (essential!)
)
SELECT n FROM numbers;

Base case: the first rows, without recursion.
Recursive step: it runs over and over on the rows already generated.
Stop condition: without it, the recursion would be infinite.

Examples

Create a view and query it

CREATE VIEW expensive_products AS
SELECT name, category, price
FROM products
WHERE price >= 200;

SELECT * FROM expensive_products ORDER BY price DESC;

Generate the series 1..5 with a recursive CTE

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers ORDER BY n;