MariaDB Tutorial / Break Queries into Named Steps with WITH and CTEs

A common table expression (CTE), defined with WITH, names an intermediate query result within one SQL statement. It can separate complex conditions and aggregation into readable steps, but does not create a permanent table or automatically guarantee faster execution.

Give each intermediate result one responsibility

Selecting paid orders, calculating order goods totals and adding customer names in a single nested expression can obscure conditions and row grain. Here paid_orders represents qualifying orders and order_totals represents one goods amount per order. Each name should have explainable columns, keys and row meaning. After splitting intermediate results, add rankings and running totals using Compare ROW_NUMBER, RANK, and DENSE_RANK and Calculate Running Totals and Compare Rows with LAG and LEAD; Query Hierarchies and Prevent Cycles with Recursive CTEs demonstrates self-referencing recursion.

Check the MariaDB documentation for the syntax and conditions used here: WITH.

Prepare an independent order fixture

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c31_customers, c31_products, c31_orders, c31_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c31_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c31_order_customer FOREIGN KEY (customer_id)
  REFERENCES c31_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c31_item_order FOREIGN KEY (order_id)
  REFERENCES c31_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c31_item_product FOREIGN KEY (product_id)
  REFERENCES c31_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c31_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c31_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c31_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c31_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Three orders are paid and one is canceled. Summarizing the items per order first makes the join-multiplication problem easier to trace. Check SELECT VERSION() and the WITH syntax supported by that version.

Define multiple CTEs before the final SELECT

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
 SELECT order_id,SUM(quantity*unit_price) AS goods_total
 FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;

Write WITH once at the start, separate the named definitions with commas and place each SELECT inside AS parentheses. The final SELECT follows the definitions. A semicolon after an intermediate definition ends the statement, making the name unavailable to a subsequent query.

Verify intermediate rows before the final result

Run each CTE’s inner SELECT independently if totals differ. paid_orders should contain IDs 101, 102 and 103. The unfiltered order_totals can contain four totals, including canceled order 104, before the final join restricts the population. Be explicit about the stage at which cancellation is excluded.

WITH paid_orders AS (
 SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;

Alternatively, keep a CTE definition and temporarily change the final SELECT to inspect it. Changing the intended output from one order to one customer still requires an appropriate aggregation; a descriptive name does not change the grain automatically.

Understand statement scope and optimizer choices

A CTE exists for its SQL statement, not as a permanent object usable by the next statement. For a definition reused across statements, consider a VIEW; for stored temporary rows, consider a suitable temporary-table design.

The optimizer can merge or materialize intermediate query structures according to the query and version. WITH does not promise a single evaluation or faster execution. Evaluate readability separately from performance and inspect EXPLAIN when performance matters. Recursive CTEs are a separate topic from this nonrecursive decomposition.

Check names, columns and aggregate grain

Inspect parentheses, commas and semicolons first for syntax errors. For missing columns, inspect the CTE SELECT list. Prefer explicit columns and names that do not obscure base tables. There is no permanent CTE object to drop; only the practice base tables need cleanup.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;