asopi tech
asopi techIndie Developer
Alopex DB v0.8.16 Tutorial, Part 3: CASE, Set Operations, CTEs, and Window Functions

[October 2026 edition]

Alopex DB v0.8.16 Tutorial, Part 3: CASE, Set Operations, CTEs, and Window Functions

Published: Oct 20, 2026
Reading time: ~16 min

Part 2 covered SELECT, WHERE, joins, and aggregation. Part 3 adds four SQL features.

The SQL in this part was verified by installing the published v0.8.16 package and running it. The same queries produce the same results through the library, embedded, server, and cluster-aware modes. This part concentrates on query syntax.

  • CASE expressions assign values according to conditions.
  • Set operations combine two query results.
  • WITH clauses give names to queries.
  • Window functions calculate rankings and running totals without removing rows.

The examples use the same articles table as Part 2.

1. Environment and setup

ItemValue
Alopex DB0.8.16 (published on PyPI and crates.io)
Python3.13.16
Rust1.97.0
OSLinux (glibc 2.39)
Verification dateOctober 20, 2026

Create the table from Part 2.

from alopex import Database

db = Database.new()
db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  views INTEGER,
  rating REAL
)""")

rows = [
    "(1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
    "(2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
    "(3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
    "(4, '組み込みDBの選び方', 'ren', 600, 4.0)",
    "(5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
]
for row in rows:
    db.execute_sql(f"INSERT INTO articles VALUES {row}")

The data has three useful properties. Two rows have rating = 4.5, creating a tie in rankings. The rating for id = 5 is NULL, so aggregates exclude it. The most-viewed and highest-rated articles are not identical, producing different sets for the two filters.

2. Assigning values with CASE

Classify articles by view count.

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN '人気' ELSE '通常' END AS band
FROM articles
ORDER BY id
"""))
[{'id': 1, 'band': '人気'},
 {'id': 2, 'band': '通常'},
 {'id': 3, 'band': '人気'},
 {'id': 4, 'band': '通常'},
 {'id': 5, 'band': '通常'}]

The form is CASE WHEN condition THEN value ELSE value END. A matching condition produces the THEN value; otherwise, the expression produces the ELSE value.

Any number of WHEN branches can appear. They are evaluated from top to bottom, and the first match is used.

Omitting ELSE

ELSE is optional.

print(db.execute_sql("""
SELECT id,
       CASE WHEN rating > 4.0 THEN '高評価' END AS top
FROM articles
ORDER BY id
"""))
[{'id': 1, 'top': None},
 {'id': 2, 'top': '高評価'},
 {'id': 3, 'top': '高評価'},
 {'id': 4, 'top': None},
 {'id': 5, 'top': None}]

Rows that match no WHEN branch receive NULL. The rating for id = 5 is NULL, so its comparison is not true and its result is also NULL.

Resolving branch types

Branches with different numeric types are converted to a common numeric type.

print(db.execute_sql("""
SELECT id,
       CASE WHEN views >= 1000 THEN 1 ELSE 0.5 END AS score
FROM articles
ORDER BY id
"""))
[{'id': 1, 'score': 1.0},
 {'id': 2, 'score': 0.5},
 {'id': 3, 'score': 1.0},
 {'id': 4, 'score': 0.5},
 {'id': 5, 'score': 0.5}]

Although THEN 1 is an integer, the result is 1.0. Because ELSE 0.5 is floating point, the entire result column becomes floating point.

An incompatible combination is rejected before execution.

db.execute_sql("SELECT CASE WHEN TRUE THEN 1 ELSE 'text' END")
error[ALOPEX-T001]: type mismatch: expected Integer, found Text at line 1, column 35

Integer and text values have no common result type.

Evaluating only the matching branch

CASE stops evaluating after the first matching WHEN.

print(db.execute_sql("SELECT CASE WHEN TRUE THEN 7 ELSE 1 / 0 END AS v"))
[{'v': 7}]

The division by zero in ELSE does not raise an error because WHEN TRUE matches first.

3. Referring to SELECT aliases from later clauses

Part 2 introduced aliases such as AS n. ORDER BY and HAVING can refer to these aliases by name.

print(db.execute_sql("""
SELECT author, SUM(views) AS total
FROM articles
GROUP BY author
HAVING total >= 2000
ORDER BY author
"""))
[{'author': 'mio', 'total': 2000},
 {'author': 'ren', 'total': 2100}]

HAVING total >= 2000 has the same meaning as HAVING SUM(views) >= 2000, avoiding repetition of the aggregate expression.

Aliases are visible to ORDER BY and HAVING, but not to WHERE or GROUP BY.

db.execute_sql("SELECT views AS v FROM articles WHERE v > 1000")
error[ALOPEX-C003]: column 'v' not found in table 'articles' at line 1, column 39

Under the SQL evaluation order, WHERE and GROUP BY run before SELECT, so the alias does not exist when their names are resolved.

An alias wins when names collide.

print(db.execute_sql("SELECT views AS id FROM articles ORDER BY id"))
[{'id': 300}, {'id': 600}, {'id': 800}, {'id': 1200}, {'id': 1500}]

ORDER BY id sorts by views, which was aliased as id, rather than by the primary key. Qualify the original column as articles.id when that is the intended ordering.

4. UNION, INTERSECT, and EXCEPT

Query articles with many views and articles with high ratings separately.

print(db.execute_sql("SELECT id FROM articles WHERE views >= 800 ORDER BY id"))
print(db.execute_sql("SELECT id FROM articles WHERE rating >= 4.0 ORDER BY id"))
[{'id': 1}, {'id': 2}, {'id': 3}]
[{'id': 2}, {'id': 3}, {'id': 4}]

The left result is {1, 2, 3}, and the right is {2, 3, 4}. Combine them with four operators.

left = "SELECT id FROM articles WHERE views >= 800"
right = "SELECT id FROM articles WHERE rating >= 4.0"

for op in ["UNION", "UNION ALL", "INTERSECT", "EXCEPT"]:
    print(op, db.execute_sql(f"{left} {op} {right} ORDER BY id"))
UNION [{'id': 1}, {'id': 2}, {'id': 3}, {'id': 4}]
UNION ALL [{'id': 1}, {'id': 2}, {'id': 2}, {'id': 3}, {'id': 3}, {'id': 4}]
INTERSECT [{'id': 2}, {'id': 3}]
EXCEPT [{'id': 1}]
OperatorResultMeaning
UNION{1, 2, 3, 4}Combine both results and remove duplicates
UNION ALL6 rowsCombine both results and retain duplicates
INTERSECT{2, 3}Keep values present in both results
EXCEPT{1}Keep values present on the left but absent on the right

Reversing the operands changes EXCEPT.

print(db.execute_sql(f"{right} EXCEPT {left} ORDER BY id"))
[{'id': 4}]

The remaining row, id = 4, has a high rating but fewer views.

Chaining three or more operators

INTERSECT binds more tightly than UNION and EXCEPT. Operators at the same precedence are evaluated from left to right, and ALL applies only to the operator where it appears.

print(db.execute_sql("SELECT 1 AS v UNION ALL SELECT 1 UNION SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 1 UNION ALL SELECT 1"))
print(db.execute_sql("SELECT 1 AS v UNION SELECT 2 INTERSECT SELECT 2"))
[{'v': 1}]
[{'v': 1}, {'v': 1}]
[{'v': 1}, {'v': 2}]

In the first query, the final UNION removes duplicates from the entire result, leaving one row. In the second, UNION first reduces the result to one row and UNION ALL then retains the duplicate, leaving two. In the third, 2 INTERSECT 2 is evaluated first.

NULL and incompatible result shapes

NULL is treated as a value for set operations.

print(db.execute_sql("SELECT rating FROM articles UNION SELECT rating FROM articles"))
[{'rating': 3.5}, {'rating': 4.5}, {'rating': 4.0}, {'rating': None}]

Two rows have rating = 4.5, but UNION reduces them to one. NULL values are also considered duplicates and collapse into one row.

Different column counts or incompatible types are rejected before execution.

db.execute_sql("SELECT id FROM articles UNION SELECT id, title FROM articles")
error[ALOPEX-T008]: set operation column count mismatch: left 1, right 2 at line 1, column 25
db.execute_sql("SELECT id FROM articles UNION SELECT title FROM articles")
error[ALOPEX-T001]: type mismatch: expected Integer, found Text at line 1, column 25

5. Naming a query with WITH

A WITH clause before a SELECT gives a subquery a name that the main query can reference.

WITH name AS (SELECT ...)
SELECT ... FROM name;

The name AS (...) definition is a common table expression, or CTE.

Name the popular articles and join them to articles by the same author.

print(db.execute_sql("""
WITH popular AS (
  SELECT id, author FROM articles WHERE views >= 800
)
SELECT articles.id AS aid, popular.id AS pid
FROM articles
JOIN popular ON articles.author = popular.author
ORDER BY aid, pid
"""))
[{'aid': 1, 'pid': 1}, {'aid': 1, 'pid': 2},
 {'aid': 2, 'pid': 1}, {'aid': 2, 'pid': 2},
 {'aid': 3, 'pid': 3}, {'aid': 4, 'pid': 3}]

A CTE joins like a table. Because mio has two articles on each side, their combinations produce four rows. A CTE does not remove duplicates automatically; use DISTINCT or GROUP BY when uniqueness is required.

Multiple CTE definitions can be listed, and a later definition can refer to one defined earlier.

Reusing a table name

A CTE hides a base table with the same name for the duration of the statement.

print(db.execute_sql("""
WITH articles AS (SELECT id + 100 AS id FROM articles WHERE id = 1)
SELECT id FROM articles
"""))
[{'id': 101}]

The FROM articles inside the CTE definition refers to the base table. The outer FROM articles refers to the CTE. Use distinct names unless this shadowing is intentional.

Self-referencing CTEs

A CTE can refer to itself when declared with WITH RECURSIVE. Recursive CTEs are used for queries whose hierarchy depth is not known in advance, such as walking an organization to its root manager or expanding a bill of materials.

WITH RECURSIVE is available from v0.8.7. Write the query that produces the first rows before UNION ALL, and the query that refers to the CTE itself after it.

print(db.execute_sql("""
WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter ORDER BY n
"""))
[{'n': 1}, {'n': 2}, {'n': 3}, {'n': 4}, {'n': 5}]

SELECT 1 produces the first row. While n < 5 holds, each step builds the next row from the rows added in the previous step, and the query stops when no new row appears. With UNION, rows that already appeared are not added again. UNION ALL keeps duplicates.

You can also walk a hierarchy. This example climbs from the row with id = 3 up to the root.

db.execute_sql("CREATE TABLE employees (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT)")
db.execute_sql("""INSERT INTO employees VALUES
  (1, NULL, 'root'), (2, 1, 'manager'), (3, 2, 'worker')""")
print(db.execute_sql("""
WITH RECURSIVE ancestors AS (
  SELECT id, parent_id, name FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.parent_id, e.name
  FROM employees e JOIN ancestors a ON e.id = a.parent_id
)
SELECT id, name FROM ancestors ORDER BY id
"""))
[{'id': 1, 'name': 'root'}, {'id': 2, 'name': 'manager'}, {'id': 3, 'name': 'worker'}]

A query that never ends is stopped by limits. Reaching 1,000 iterations, 100,000 accumulated rows, or the query memory limit returns ALOPEX-E003.

db.execute_sql("""
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n FROM c)
SELECT n FROM c
""")
error[ALOPEX-E003]: resource exhausted: recursive CTE 'c' reached iteration limit 1000

The shapes you can write are limited. The following return ALOPEX-F001 and state that they are unsupported:

  • listing a self-referencing CTE together with other CTEs in one WITH RECURSIVE (including mutual references)
  • referring to the CTE more than once in the recursive part
  • a subquery, nested WITH, or set operation in the recursive part
  • ORDER BY, LIMIT, or OFFSET on the CTE body

Alopex DB never ignores RECURSIVE and executes one level. A WITH RECURSIVE that does not refer to itself runs as an ordinary CTE.

Referring to an undefined CTE in FROM returns ALOPEX-C001, the same error used for an invalid table name.

6. Rankings and running totals with window functions

GROUP BY combines rows, so it cannot display an aggregate beside every original row. Window functions calculate over a group while preserving the rows.

print(db.execute_sql("""
SELECT id,
       RANK() OVER (ORDER BY rating) AS rk,
       DENSE_RANK() OVER (ORDER BY rating) AS dk,
       SUM(views) OVER (ORDER BY id) AS running
FROM articles
ORDER BY id
"""))
[{'id': 1, 'rk': 1, 'dk': 1, 'running': 1200},
 {'id': 2, 'rk': 3, 'dk': 3, 'running': 2000},
 {'id': 3, 'rk': 3, 'dk': 3, 'running': 3500},
 {'id': 4, 'rk': 2, 'dk': 2, 'running': 4100},
 {'id': 5, 'rk': 5, 'dk': 4, 'running': 4400}]

All five input rows remain in the result. Unlike GROUP BY, the window calculation does not reduce them.

The two rows with rating = 4.5, id = 2 and id = 3, tie. RANK leaves a gap after a tie and gives the final row rank 5, while DENSE_RANK does not leave gaps and gives it rank 4.

running is the cumulative sum in id order. Supported ranking functions are ROW_NUMBER, RANK, and DENSE_RANK, and LAG and LEAD read adjacent rows. Supported aggregate window functions are SUM, COUNT, AVG, MIN, and MAX.

The ranking and cumulative sums here were verified by installing the published v0.8.16 and running them, recorded below. Ranks and the running total come back together while every row is retained.

Running RANK, DENSE_RANK, and a running total together in v0.8.16

Defining the aggregation scope

The contents of OVER define the aggregation scope.

print(db.execute_sql("SELECT id, SUM(views) OVER () AS grand FROM articles ORDER BY id"))
print(db.execute_sql("SELECT id, SUM(views) OVER (PARTITION BY author) AS by_author FROM articles ORDER BY id"))
[{'id': 1, 'grand': 4400}, {'id': 2, 'grand': 4400}, {'id': 3, 'grand': 4400}, {'id': 4, 'grand': 4400}, {'id': 5, 'grand': 4400}]
[{'id': 1, 'by_author': 2000}, {'id': 2, 'by_author': 2000}, {'id': 3, 'by_author': 2100}, {'id': 4, 'by_author': 2100}, {'id': 5, 'by_author': 300}]

OVER () sums all rows. PARTITION BY author sums each author’s rows. The earlier OVER (ORDER BY id) accumulates from the first row through the current row.

The scope follows two rules.

  1. Without ORDER BY inside OVER, the scope is the entire window.
  2. With ORDER BY inside OVER, the scope runs from the first row through the current row.

The same SUM(views) therefore changes meaning according to the contents of OVER.

NULL handling

Window aggregates ignore NULL values.

print(db.execute_sql("SELECT id, SUM(rating) OVER (PARTITION BY author) AS r FROM articles ORDER BY id"))
[{'id': 1, 'r': 8.0}, {'id': 2, 'r': 8.0},
 {'id': 3, 'r': 8.5}, {'id': 4, 'r': 8.5},
 {'id': 5, 'r': None}]

sora has one article whose rating is NULL. With no value to sum, the result is NULL rather than zero.

Using the previous row

Use LAG and LEAD to read adjacent rows directly. LAG(column) returns the value from the previous row, and LEAD(column) returns it from the next row. A difference from the previous row no longer needs a self-join.

print(db.execute_sql("""
SELECT id, views - LAG(views) OVER (ORDER BY id) AS diff
FROM articles
ORDER BY id
"""))
[{'id': 1, 'diff': None},
 {'id': 2, 'diff': -400},
 {'id': 3, 'diff': 700},
 {'id': 4, 'diff': -900},
 {'id': 5, 'diff': -300}]

id = 1 has no previous row, so LAG returns NULL. Pass a default as the third argument to return it instead of NULL.

print(db.execute_sql("SELECT id, LEAD(views, 1, 0) OVER (ORDER BY id) AS nxt FROM articles ORDER BY id"))
[{'id': 1, 'nxt': 800}, {'id': 2, 'nxt': 1500}, {'id': 3, 'nxt': 600}, {'id': 4, 'nxt': 300}, {'id': 5, 'nxt': 0}]

The second argument is the offset in rows, and it defaults to 1. id = 5 has no next row, so the default 0 is returned.

Setting the range in rows

You can state the aggregation range with ROWS BETWEEN or RANGE BETWEEN. This example adds the current row and the one before it.

print(db.execute_sql("""
SELECT id, SUM(views) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS s2
FROM articles
ORDER BY id
"""))
[{'id': 1, 's2': 1200}, {'id': 2, 's2': 2000}, {'id': 3, 's2': 2300}, {'id': 4, 's2': 2100}, {'id': 5, 's2': 900}]

id = 1 has no previous row, so it is just 1200. id = 3 is 800 + 1500 = 2300 from id = 2 and id = 3. An aggregate with ORDER BY and no explicit range is a running total from the first row to the current row. Writing RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives the same result.

7. Types and values

An unexpected aggregate result can come from its type. The views column is INTEGER, and rating is REAL. The following queries show where those types affect results.

print(db.execute_sql("SELECT SUM(views) AS total FROM articles"))
print(db.execute_sql("SELECT id, views * 1.5 AS scaled FROM articles WHERE id <= 2 ORDER BY id"))
print(db.execute_sql("SELECT pg_typeof(views) AS iv, pg_typeof(rating) AS rv FROM articles WHERE id = 1"))
print(db.execute_sql("SELECT COUNT(rating) AS cnt, COUNT(*) AS all_rows FROM articles"))
[{'total': 4400}]
[{'id': 1, 'scaled': 1800.0}, {'id': 2, 'scaled': 1200.0}]
[{'iv': 'integer', 'rv': 'real'}]
[{'cnt': 4, 'all_rows': 5}]
  1. A sum of integers remains an integer. SUM(views) returns 4400, not 4400.0.
  2. Arithmetic mixing integers and floating-point values produces floating point. views * 1.5 returns 1800.0.
  3. pg_typeof() reports column types. views is integer, while rating is real. REAL is an alias for FLOAT, a four-byte f32. Declare a column as DOUBLE when double precision is required.
  4. COUNT(column) excludes NULL. COUNT(rating) is 4, while COUNT(*) is 5. Choose according to whether you need the number of rows or the number of non-NULL values.

8. Window functions and WITH from Rust

Rust passes window functions and WITH clauses directly to execute_sql(). Reuse the show function from Part 2.

use alopex_embedded::{Database, SqlResult};
use std::path::Path;

fn show(db: &Database, sql: &str) -> Result<(), Box<dyn std::error::Error>> {
    if let SqlResult::Query(q) = db.execute_sql(sql)? {
        let names: Vec<&str> = q.columns.iter().map(|c| c.name.as_str()).collect();
        println!("{}", names.join(" | "));
        for row in &q.rows {
            let cells: Vec<String> = row.iter().map(|v| format!("{v:?}")).collect();
            println!("{}", cells.join(" | "));
        }
    }
    Ok(())
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let db = Database::open(Path::new("./notes-db"))?;
    db.execute_sql("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, author TEXT, views INTEGER, rating REAL)")?;
    for sql in [
        "INSERT INTO articles VALUES (1, 'Rustで書くLSMツリー', 'mio', 1200, 3.5)",
        "INSERT INTO articles VALUES (2, 'SQLパーサをNimで実装する', 'mio', 800, 4.5)",
        "INSERT INTO articles VALUES (3, 'ベクトル検索の基礎', 'ren', 1500, 4.5)",
        "INSERT INTO articles VALUES (4, '組み込みDBの選び方', 'ren', 600, 4.0)",
        "INSERT INTO articles VALUES (5, 'WALとクラッシュ復旧', 'sora', 300, NULL)",
    ] {
        db.execute_sql(sql)?;
    }

    show(&db, "SELECT title, rating,
                      RANK() OVER (ORDER BY rating DESC) AS r,
                      DENSE_RANK() OVER (ORDER BY rating DESC) AS dr
               FROM articles")?;
    Ok(())
}
title | rating | r | dr
Text("Rustで書くLSMツリー") | Float(3.5) | BigInt(4) | BigInt(3)
Text("SQLパーサをNimで実装する") | Float(4.5) | BigInt(1) | BigInt(1)
Text("ベクトル検索の基礎") | Float(4.5) | BigInt(1) | BigInt(1)
Text("組み込みDBの選び方") | Float(4.0) | BigInt(3) | BigInt(2)
Text("WALとクラッシュ復旧") | Null | BigInt(5) | BigInt(4)

The rankings match the Python results. Both rows with rating = 4.5 rank first. RANK assigns the next row rank three, while DENSE_RANK assigns rank two. The NULL rating for id = 5 sorts last.

The types from the previous section appear directly in Rust output. rating was declared as REAL, so it returns Float, while rankings return BigInt. Replacing the query with AVG and COUNT shows the aggregate result types.

show(&db, "SELECT AVG(rating) AS avg_rating, COUNT(rating) AS c_rating, COUNT(*) AS c_all
           FROM articles")?;
avg_rating | c_rating | c_all
Double(4.125) | BigInt(4) | BigInt(5)

The rating column is Float (32-bit), while AVG returns Double (64-bit) because the average is calculated at 64-bit precision. COUNT(rating) is four and COUNT(*) is five because NULL is excluded from the former.

Python returns only the numeric value 4.125, so the distinction is not visible there. Rust returns the type with the value, making the Float and Double result types explicit. If a column type is unexpected, inspect data_type in q.columns as well.

9. SQL used in this part

SyntaxRole
CASE WHEN ... THEN ... ELSE ... ENDAssign a value according to a condition
UNION / UNION ALLCombine two results
INTERSECT / EXCEPTCalculate intersection or difference
WITH name AS (...)Give a query a name
RANK() / DENSE_RANK() / ROW_NUMBER()Assign rankings
SUM(...) OVER (...)Aggregate while preserving rows
LAG(...) / LEAD(...)Read the previous or next row
ROWS BETWEEN ... / RANGE BETWEEN ...State the aggregation range
HAVING alias / ORDER BY aliasUse a name assigned by SELECT

Part 4 covers vector search and DataFrames.

References