Chapter 29 of 35

Common Table Expressions (WITH)

In the Subqueries chapter we put a whole SELECT inside the FROM clause (a derived table) to compute something first and use it after. It works, but as queries grow, subqueries nest inside subqueries and the SQL becomes hard to read from the inside out.

A Common Table Expression (CTE) lets you pull that inner query out, give it a name at the top with the WITH keyword, and then use it like a table in the main query. Your SQL then reads top to bottom, step by step, like a recipe.

A CTE is a temporary, named result set defined with WITH at the start of a statement, which exists only while that statement runs.

CTEs also unlock something subqueries cannot do: recursive queries, which can generate series of numbers or dates and walk hierarchies like our employee-manager tree. MySQL supports CTEs from version 8.0. All examples use the shared company_db database, with real MySQL 8.4 output.


Basic Syntax

WITH cte_name AS (
    SELECT ...          -- any query
)
SELECT ...
FROM cte_name ...;      -- the main query uses it like a table

Three rules to remember:

  • The WITH clause comes first, before the main SELECT.

  • The CTE exists only for that one statement. The next statement cannot see it.

  • A CTE can be used in the main query as many times as you like.


Your First CTE: Employees Above Their Department Average

A classic question: which employees earn more than the average salary of their own department? We first need the average per department, then compare each employee against it.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
)
SELECT e.name, e.dept_id, e.salary, ROUND(d.avg_salary, 2) AS dept_avg
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary
ORDER BY e.dept_id;
+--------------+---------+-----------+-----------+
| name         | dept_id | salary    | dept_avg  |
+--------------+---------+-----------+-----------+
| Aarav Sharma |      10 | 150000.00 | 113333.33 |
| Sneha Kapoor |      20 | 110000.00 |  76666.67 |
| Karan Patel  |      30 |  70000.00 |  59000.00 |
+--------------+---------+-----------+-----------+
3 rows in set (0.00 sec)

Read it in two steps:

  1. Step 1 (the CTE dept_avg): one row per department with its average salary: 10 → 113333.33, 20 → 76666.67, 30 → 59000, 40 → 82000.

  2. Step 2 (the main query): join each employee with their department's average and keep those above it. Arjun is the only person in Marketing, so he equals the average and is not "above" it.

Here is the same query written with a derived table, as in the Subqueries chapter:

SELECT e.name, e.dept_id, e.salary, ROUND(d.avg_salary, 2) AS dept_avg
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_salary
      FROM employees
      WHERE dept_id IS NOT NULL
      GROUP BY dept_id) AS d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary
ORDER BY e.dept_id;

It returns exactly the same three rows. For this small example both are fine. The CTE version wins as soon as there are several steps, or the same intermediate result is needed twice.

And to prove that a CTE lives only inside its statement:

SELECT * FROM dept_avg;
ERROR 1146 (42S02): Table 'company_db.dept_avg' doesn't exist

Multiple CTEs: Building a Query Step by Step

You can define several CTEs, separated by commas, under a single WITH. A later CTE may use an earlier one.

Question: which customers have spent more than the average customer (ignoring cancelled orders)?

WITH customer_totals AS (            -- step 1: total per customer
  SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'Cancelled' AND customer_id IS NOT NULL
  GROUP BY customer_id
),
overall AS (                         -- step 2: average of those totals
  SELECT AVG(total_spent) AS avg_spent FROM customer_totals
)
SELECT c.name, t.orders, t.total_spent, o.avg_spent   -- step 3: compare
FROM customer_totals t
JOIN customers c ON c.customer_id = t.customer_id
CROSS JOIN overall o
WHERE t.total_spent > o.avg_spent
ORDER BY t.total_spent DESC;
+--------------+--------+-------------+-------------+
| name         | orders | total_spent | avg_spent   |
+--------------+--------+-------------+-------------+
| Rahul Verma  |      2 |     8100.00 | 6062.500000 |
| Kavya Menon  |      2 |     7900.00 | 6062.500000 |
| Aditya Joshi |      1 |     7300.00 | 6062.500000 |
+--------------+--------+-------------+-------------+
3 rows in set (0.00 sec)

Let us check by looking at step 1 on its own (a great habit while debugging: run each CTE separately):

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'Cancelled' AND customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT * FROM customer_totals ORDER BY customer_id;
+-------------+-------------+--------+
| customer_id | total_spent | orders |
+-------------+-------------+--------+
|         101 |     8100.00 |      2 |
|         102 |     7900.00 |      2 |
|         103 |     7300.00 |      1 |
|         104 |      950.00 |      1 |
+-------------+-------------+--------+
4 rows in set (0.00 sec)

Average = (8100 + 7900 + 7300 + 950) / 4 = 24250 / 4 = 6062.50. Only Neha (104) is below it. Rahul's cancelled order 1003 (1200) and the guest order 1008 were excluded by the WHERE clause in step 1.

Notice the shape: WITH a AS (...), b AS (...) SELECT .... There is only one WITH keyword, and no comma after the last CTE. Each CTE name must be unique in the statement:

WITH cte AS (SELECT 1 AS x), cte AS (SELECT 2 AS x) SELECT * FROM cte;
ERROR 1066 (42000): Not unique table/alias: 'cte'

Using the Same CTE Twice

A big advantage over derived tables: a CTE can be referenced several times, even joined with itself. Let us compare each month's revenue with the previous month's:

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month_start, SUM(amount) AS revenue
  FROM orders
  WHERE status <> 'Cancelled'
  GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')
)
SELECT cur.month_start, cur.revenue, prev.revenue AS prev_revenue,
       cur.revenue - prev.revenue AS change_amt
FROM monthly cur
LEFT JOIN monthly prev ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;
+-------------+---------+--------------+------------+
| month_start | revenue | prev_revenue | change_amt |
+-------------+---------+--------------+------------+
| 2024-01-01  | 7300.00 |         NULL |       NULL |
| 2024-02-01  | 7300.00 |      7300.00 |       0.00 |
| 2024-03-01  | 9650.00 |      7300.00 |    2350.00 |
| 2024-04-01  |  800.00 |      9650.00 |   -8850.00 |
+-------------+---------+--------------+------------+
4 rows in set (0.00 sec)

monthly is written once and used twice, as cur and prev. January has no previous month, so the LEFT JOIN gives NULL. With derived tables you would have to paste the whole GROUP BY query twice. (In the next chapter, Window Functions, you will see an even shorter way to get "the previous row's value".)


Recursive CTEs

A recursive CTE is a CTE that refers to itself. It is how SQL handles problems of the form "start here, then keep taking the next step until there are no more steps": counting, generating dates, and walking trees.

WITH RECURSIVE cte_name AS (
    SELECT ...                    -- 1. anchor member: the starting row(s)
    UNION ALL
    SELECT ... FROM cte_name ...  -- 2. recursive member: next rows, built from the previous ones
       WHERE ...                  -- 3. stop condition
)
SELECT ... FROM cte_name;

Generating Numbers 1 to 5

WITH RECURSIVE numbers AS (
  SELECT 1 AS n                                -- anchor
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5        -- recursive member
)
SELECT n FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

How MySQL evaluates it:

Iteration   Input rows (from previous step)   New rows produced
---------   -------------------------------   -----------------
anchor      -                                 n = 1
1           n = 1    (1 < 5, so 1 + 1)        n = 2
2           n = 2                             n = 3
3           n = 3                             n = 4
4           n = 4                             n = 5
5           n = 5    (5 < 5 is false)         no rows  -> STOP

Result = all rows produced: 1, 2, 3, 4, 5

Important details:

  • Each iteration sees only the rows produced by the previous iteration, not the whole result so far.

  • Recursion stops when an iteration produces no new rows.

  • The anchor and recursive parts are joined with UNION ALL (or UNION, which also removes duplicates).

  • The keyword is WITH RECURSIVE. If any CTE in the WITH list is recursive, write RECURSIVE once at the top.

Forgetting the Stop Condition

Without WHERE n < 5, the recursion would never end. MySQL protects you with a limit, cte_max_recursion_depth (1000 by default):

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers
)
SELECT COUNT(*) FROM numbers;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

The same limit stops a perfectly correct query that legitimately needs more steps, for example WHERE n < 2000 fails with the same error. If you really need more, raise the limit for your session:

SET SESSION cte_max_recursion_depth = 5000;

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 2000
)
SELECT COUNT(*), MAX(n) FROM numbers;
+----------+--------+
| COUNT(*) | MAX(n) |
+----------+--------+
|     2000 |   2000 |
+----------+--------+
1 row in set (0.00 sec)

Watch the Column Width of the Anchor

The anchor member decides the data type (and length) of each column. If the recursive member produces longer values, MySQL in strict mode refuses:

WITH RECURSIVE t AS (
  SELECT 1 AS n, 'ab' AS s
  UNION ALL
  SELECT n + 1, CONCAT(s, 'ab') FROM t WHERE n < 3
)
SELECT * FROM t;
ERROR 1406 (22001): Data too long for column 's' at row 1

'ab' made s a 2-character column, and 'abab' does not fit. Fix it by casting the anchor value to a wider type:

WITH RECURSIVE t AS (
  SELECT 1 AS n, CAST('ab' AS CHAR(20)) AS s
  UNION ALL
  SELECT n + 1, CONCAT(s, 'ab') FROM t WHERE n < 3
)
SELECT * FROM t;
+------+--------+
| n    | s      |
+------+--------+
|    1 | ab     |
|    2 | abab   |
|    3 | ababab |
+------+--------+
3 rows in set (0.01 sec)

Keep this in mind for the hierarchy "path" column below.


Recursive CTE Use Case 1: Filling Gaps in a Date Series

In the Date & Time Functions chapter, our "revenue per month" report simply skipped months with no orders. A manager wants to see May and June 2024 with zero, not missing. The fix: generate every month with a recursive CTE, then LEFT JOIN the orders to it.

WITH RECURSIVE months AS (
  SELECT DATE('2024-01-01') AS month_start
  UNION ALL
  SELECT month_start + INTERVAL 1 MONTH
  FROM months
  WHERE month_start < '2024-06-01'
)
SELECT DATE_FORMAT(m.month_start, '%Y-%m') AS month,
       COUNT(o.order_id)          AS orders,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM months m
LEFT JOIN orders o
  ON  o.order_date >= m.month_start
  AND o.order_date <  m.month_start + INTERVAL 1 MONTH
  AND o.status <> 'Cancelled'
GROUP BY m.month_start
ORDER BY m.month_start;
+---------+--------+---------+
| month   | orders | revenue |
+---------+--------+---------+
| 2024-01 |      2 | 7300.00 |
| 2024-02 |      1 | 7300.00 |
| 2024-03 |      3 | 9650.00 |
| 2024-04 |      1 |  800.00 |
| 2024-05 |      0 |    0.00 |
| 2024-06 |      0 |    0.00 |
+---------+--------+---------+
6 rows in set (0.00 sec)

Points to notice:

  • The anchor uses DATE('2024-01-01') so the column is a real DATE, not a string.

  • The status filter is in the ON clause, not WHERE. A WHERE on o.status would remove the NULL rows for May and June and turn the LEFT JOIN back into an inner join (a trap from the LEFT, RIGHT & FULL OUTER JOIN chapter).

  • COUNT(o.order_id) counts 0 for empty months, while COUNT(*) would count the single NULL-padded row as 1. COALESCE turns the NULL sum into 0.


Recursive CTE Use Case 2: Walking a Hierarchy

Our employees table stores a tree: each employee's manager_id points to another employee. In the SELF JOIN & CROSS JOIN chapter we used a self join to find each employee's direct manager, but one self join only goes one level. How many levels does the company have? A self join cannot answer that in general, because you would need one join per level. A recursive CTE handles any depth.

The Full Org Chart, Top Down

WITH RECURSIVE org AS (
  -- anchor: the top boss (no manager)
  SELECT emp_id, name, manager_id, 1 AS level,
         CAST(name AS CHAR(200)) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- recursive: employees whose manager is already in org
  SELECT e.emp_id, e.name, e.manager_id, o.level + 1,
         CONCAT(o.path, ' > ', e.name)
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, level,
       CONCAT(REPEAT('    ', level - 1), name) AS org_chart,
       path
FROM org
ORDER BY path;
+--------+-------+----------------------+--------------------------------------------+
| emp_id | level | org_chart            | path                                       |
+--------+-------+----------------------+--------------------------------------------+
|      1 |     1 | Aarav Sharma         | Aarav Sharma                               |
|      9 |     2 |     Arjun Reddy      | Aarav Sharma > Arjun Reddy                 |
|     10 |     2 |     Ishita Gupta     | Aarav Sharma > Ishita Gupta                |
|      7 |     2 |     Karan Patel      | Aarav Sharma > Karan Patel                 |
|      8 |     3 |         Meera Nair   | Aarav Sharma > Karan Patel > Meera Nair    |
|      2 |     2 |     Priya Iyer       | Aarav Sharma > Priya Iyer                  |
|      3 |     2 |     Rohan Mehta      | Aarav Sharma > Rohan Mehta                 |
|      4 |     2 |     Sneha Kapoor     | Aarav Sharma > Sneha Kapoor                |
|      6 |     3 |         Ananya Das   | Aarav Sharma > Sneha Kapoor > Ananya Das   |
|      5 |     3 |         Vikram Singh | Aarav Sharma > Sneha Kapoor > Vikram Singh |
+--------+-------+----------------------+--------------------------------------------+
10 rows in set (0.00 sec)

Trace of the iterations:

anchor       : Aarav (manager_id IS NULL)                         level 1
iteration 1  : employees whose manager is Aarav (emp 1)
               -> Priya, Rohan, Sneha, Karan, Arjun, Ishita         level 2
iteration 2  : employees whose manager is one of those six
               -> Vikram, Ananya (manager Sneha), Meera (manager Karan)  level 3
iteration 3  : employees whose manager is Vikram, Ananya or Meera
               -> none, STOP

Why ORDER BY path? Sorting by the path string puts each manager directly above their team, giving a readable org chart. The REPEAT indentation makes the levels visible. And the CAST(... AS CHAR(200)) in the anchor gives the path room to grow, as explained above.

With the tree in hand, summaries are easy, for example the number of people at each level:

WITH RECURSIVE org AS (
  SELECT emp_id, name, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, o.level + 1
  FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, COUNT(*) AS employees,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS names
FROM org
GROUP BY level
ORDER BY level;
+-------+-----------+-------------------------------------------------------------------------------+
| level | employees | names                                                                         |
+-------+-----------+-------------------------------------------------------------------------------+
|     1 |         1 | Aarav Sharma                                                                  |
|     2 |         6 | Arjun Reddy, Ishita Gupta, Karan Patel, Priya Iyer, Rohan Mehta, Sneha Kapoor |
|     3 |         3 | Ananya Das, Meera Nair, Vikram Singh                                          |
+-------+-----------+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

One Manager's Whole Team

Change the anchor and you get any subtree. Sneha's complete team (herself and everyone below her, at any depth) and its salary cost:

WITH RECURSIVE team AS (
  SELECT emp_id, name, salary FROM employees WHERE emp_id = 4
  UNION ALL
  SELECT e.emp_id, e.name, e.salary
  FROM employees e JOIN team t ON e.manager_id = t.emp_id
)
SELECT COUNT(*) AS team_size, SUM(salary) AS team_salary FROM team;
+-----------+-------------+
| team_size | team_salary |
+-----------+-------------+
|         3 |   230000.00 |
+-----------+-------------+
1 row in set (0.00 sec)

Sneha (110000) + Vikram (62000) + Ananya (58000) = 230000.

Bottom Up: The Chain of Managers

Reverse the join direction to climb the tree. Who is above Meera (emp 8), all the way to the top?

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 0 AS steps_up
  FROM employees
  WHERE emp_id = 8
  UNION ALL
  SELECT m.emp_id, m.name, m.manager_id, c.steps_up + 1
  FROM employees m
  JOIN chain c ON m.emp_id = c.manager_id
)
SELECT steps_up, emp_id, name FROM chain ORDER BY steps_up;
+----------+--------+--------------+
| steps_up | emp_id | name         |
+----------+--------+--------------+
|        0 |      8 | Meera Nair   |
|        1 |      7 | Karan Patel  |
|        2 |      1 | Aarav Sharma |
+----------+--------+--------------+
3 rows in set (0.01 sec)

Top down, the join is e.manager_id = o.emp_id ("find people whose manager I already have"). Bottom up, it is m.emp_id = c.manager_id ("find the manager of the person I already have"). The recursion stops at Aarav because his manager_id is NULL, and NULL matches nothing.

Cycles: if bad data ever made A the manager of B and B the manager of A, this recursion would loop until it hit cte_max_recursion_depth. A safety net is to add a depth guard such as WHERE o.level < 20 to the recursive member.

One More Classic: Fibonacci Numbers

You can also give the CTE's column names in parentheses after its name, instead of using aliases inside:

WITH RECURSIVE fib (n, a, b) AS (
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, b, a + b FROM fib WHERE n < 10
)
SELECT n, a AS fib FROM fib;
+------+------+
| n    | fib  |
+------+------+
|    1 |    0 |
|    2 |    1 |
|    3 |    1 |
|    4 |    2 |
|    5 |    3 |
|    6 |    5 |
|    7 |    8 |
|    8 |   13 |
|    9 |   21 |
|   10 |   34 |
+------+------+
10 rows in set (0.00 sec)

Each step carries the last two numbers (a, b) forward as (b, a + b).


CTEs With UPDATE and DELETE

In MySQL, WITH can also start an UPDATE or DELETE. Suppose HR gives a 5% raise to everyone earning less than 90% of their department's average (run this on a practice copy of the database):

WITH dept_avg AS (
  SELECT dept_id, ROUND(AVG(salary), 2) AS avg_salary
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
)
UPDATE employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
SET e.salary = e.salary * 1.05
WHERE e.salary < d.avg_salary * 0.9;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0
SELECT emp_id, name, salary FROM employees WHERE emp_id IN (2, 3, 5, 6, 8) ORDER BY emp_id;
+--------+--------------+----------+
| emp_id | name         | salary   |
+--------+--------------+----------+
|      2 | Priya Iyer   | 99750.00 |
|      3 | Rohan Mehta  | 99750.00 |
|      5 | Vikram Singh | 65100.00 |
|      6 | Ananya Das   | 60900.00 |
|      8 | Meera Nair   | 50400.00 |
+--------+--------------+----------+
5 rows in set (0.00 sec)

All five were below 90% of their department average (for example, Engineering's 113333.33 × 0.9 = 102000, and Priya earned 95000). Note that the averages are computed before any row is changed, so the raises do not affect which rows qualify.


CTE vs Subquery vs View vs Temporary Table

Derived table (subquery in FROM)

CTE

View

Temporary table

Lifetime

One query

One statement

Permanent until dropped

Until the session ends

Can be referenced more than once

No (must repeat it)

Yes

Yes

Yes, in later statements; but MySQL cannot open the same TEMPORARY table twice in one query (error 1137, "Can't reopen table")

Can be recursive

No

Yes

Only if its definition uses a recursive CTE

No

Stores data

No

No

No

Yes

Best for

Small one-off inner queries

Readable multi-step queries, hierarchies, series

Reusable logic shared by many queries and users

Large intermediate results reused across several statements

On performance: MySQL's optimizer treats a non-recursive CTE much like a derived table. It may merge it into the outer query, or materialize it once (which is helpful when the CTE is used twice). A CTE is mainly a readability tool, not a speed trick.


Common Mistakes

Mistake

What happens

Fix

Using a CTE in a later statement

Error 1146, table doesn't exist

A CTE lives for one statement; use a view or temporary table to reuse

Writing WITH before each CTE

Syntax error

One WITH, CTEs separated by commas

Comma after the last CTE

Syntax error

No comma before the main SELECT

Missing RECURSIVE for a self-referencing CTE

Error 1146: MySQL looks for a real table with the CTE's name

WITH RECURSIVE

No stop condition in the recursive member

Error 3636 after 1001 iterations

Add a WHERE that eventually fails

Anchor column too narrow

Error 1406, Data too long

CAST the anchor to a wider type

Filtering the outer-joined table in WHERE when filling gaps

Empty periods disappear again

Put that condition in the ON clause


Interview Questions

What is a CTE?

A named temporary result set defined with WITH at the start of a statement and used like a table in that statement. It exists only while the statement runs.

What is the difference between a CTE and a subquery?

Both compute an intermediate result. A CTE is defined once at the top, can be referenced multiple times, can refer to earlier CTEs, and can be recursive. A derived-table subquery is written inline and must be repeated to be reused.

What is the difference between a CTE and a view?

A view is a permanent database object that any later query can use. A CTE is part of one statement and disappears after it.

What are the parts of a recursive CTE?

An anchor member (the starting rows), UNION ALL, and a recursive member that references the CTE itself and contains a condition that eventually produces no rows, ending the recursion.

How do you find all employees under a given manager at any level?

A recursive CTE: the anchor selects the manager, and the recursive member joins employees e to the CTE on e.manager_id = cte.emp_id.

How do you generate a list of dates or numbers in MySQL?

With a recursive CTE, e.g. anchor SELECT DATE('2024-01-01') AS d and recursive member SELECT d + INTERVAL 1 DAY FROM cte WHERE d < '2024-01-31'.

How does MySQL prevent infinite recursion?

The cte_max_recursion_depth system variable (default 1000) aborts a recursive CTE that runs too many iterations, with error 3636.

Does a CTE improve performance?

Not by itself; it mainly improves readability. The optimizer may merge or materialize it; materializing once can help when the CTE is referenced several times.


Summary

  • WITH name AS (SELECT ...) defines a temporary named result for one statement.

  • Several CTEs go under one WITH, separated by commas; later ones can use earlier ones, and each can be used many times.

  • CTEs turn nested subqueries into readable, top-to-bottom steps, and each step can be tested on its own.

  • WITH RECURSIVE = anchor + UNION ALL + recursive member with a stop condition. Each iteration works on the previous iteration's rows.

  • Recursive CTEs generate number/date series (to fill gaps in reports) and walk hierarchies such as manager trees, top down or bottom up.

  • Guard rails: cte_max_recursion_depth (default 1000), and CAST the anchor's columns wide enough.

  • In MySQL, WITH can also start UPDATE and DELETE statements.

CTEs let us compute "per group" values in a separate step and join them back. In the next chapter, Window Functions, we will learn how to compute group totals, running totals and previous-row values right next to each row, without any join at all.