SQL · Theory

CTE

Theory

282/439

CTE

CTE is WITH name AS (SELECT …) SELECT … FROM name; A named subquery you can read top-down. MySQL 8+. WITH fail AS (SELECT * FROM students WHERE marks < 40) SELECT COUNT(*) FROM fail;

WITH RECURSIVE walks a tree (org chart). Don’t start there. Start with one WITH as a readable subquery.

Trap — CTE on MySQL 5.7 (not supported). Trap: recursive CTE without a stop condition.

On the example next to this theory: CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WITH fail AS (SELECT … WHERE marks < 40) SELECT * FROM fail;

CTE — sample query

-- CTE
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefWITH alias AS (query) — named subquery (MySQL 8).
RuleMakes long SQL readable. RECURSIVE is a later topic.
TrapCTE on 5.7.

SQL · Theory

CTE

← All stacks

Theory

282/439

CTE

CTE is WITH name AS (SELECT …) SELECT … FROM name; A named subquery you can read top-down. MySQL 8+. WITH fail AS (SELECT * FROM students WHERE marks < 40) SELECT COUNT(*) FROM fail;

WITH RECURSIVE walks a tree (org chart). Don’t start there. Start with one WITH as a readable subquery.

Trap — CTE on MySQL 5.7 (not supported). Trap: recursive CTE without a stop condition.

On the example next to this theory: CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

WITH fail AS (SELECT … WHERE marks < 40) SELECT * FROM fail;

CTE — sample query

-- CTE
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

CTE: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefWITH alias AS (query) — named subquery (MySQL 8).
RuleMakes long SQL readable. RECURSIVE is a later topic.
TrapCTE on 5.7.

CTE

CTE — sample query

Short notes

Questions

CTE

CTE — sample query

Short notes

Questions