Advanced SQL

What it is and why it exists

“Advanced SQL” isn’t a separate level of the SQL standard. It’s a set of query capabilities for multistage relational problems. A window function computes ranks, offsets, or cumulative values without discarding detail rows; common table expressions name the stages of a long query; recursion repeatedly expands a relation; and existence predicates express “at least one” or “none.” These tools keep work that might otherwise become an application loop inside one declarative query.

An ordinary aggregate collapses a group of input rows into one row; a window function doesn’t. A CTE isn’t a synonym for a temporary table, either. It is first a named query inside the current statement. A recursive CTE isn’t an unrestricted loop: each round must derive new rows from the previous round, and the process needs a provable stopping condition.

You meet these structures in top-N-per-group reports, cumulative metrics, adjacent-event comparisons, organization trees, category paths, missing relationships, and exclusion lists. The hard part isn’t memorizing keywords. It is defining the row set, order, boundaries, and NULL semantics precisely.

The executable examples target SQLite 3.45.1, bundled with the local Python runtime. Their core relational ideas also apply to databases such as PostgreSQL, but date functions, recursion limits, materialization behavior, and some window syntax vary by product. Rerun the tests against your target database before deployment.

How it works

A SELECT can be understood as a logical pipeline that progressively forms the result relation. The database doesn’t have to perform physical operations in this order, but logical phases determine name visibility and where expressions are legal. That is why a window result cannot appear directly in the same query level’s WHERE clause.

The optimizer may push a filter down, rewrite a join, or choose a different access path as long as the result still follows SQL semantics. Use logical order to reason about correctness and an execution plan to explain actual work. Don’t confuse the two.

Partitions, order, and frames

A window function’s OVER clause has three independent dimensions:

1. PARTITION BY divides the input into partitions that don’t affect one another. Without it, all input rows form one partition.
2. The window’s ORDER BY defines the logical order seen by ranking, offset, or cumulative calculations. It doesn’t guarantee final output order.
3. A window frame selects the segment of the current partition used to calculate the current row. Boundaries have different meanings for ROWS, GROUPS, and RANGE.

ROW_NUMBER(), RANK(), and DENSE_RANK() all depend on the window order, but they handle ties differently. ROW_NUMBER() always assigns distinct positions. RANK() gives peers the same rank and leaves gaps; DENSE_RANK() leaves no gaps. ROW_NUMBER() selects the same row reliably only when its ordering keys are unique.

Named intermediate relations

A common table expression (CTE) is defined with WITH name AS (...) and is visible only to the following statement. It is useful for naming query stages: first find eligible orders, then number them, then select the first two per group. Names improve reasoning boundaries, but they don’t automatically save a result or improve performance.

A CTE’s columns still form a relation. An outer query can refer only to columns that the CTE projects, and a filter cannot read a window value that its own query level hasn’t computed yet. To filter on a window value, calculate it in a CTE and read the alias from an outer WHERE.

Recursive relation expansion

A recursive CTE contains an anchor member and a recursive member. The anchor produces starting rows. The recursive member refers to the previous round’s output and produces the next rows. UNION ALL or UNION combines the members, and evaluation stops when a round produces no more rows.

Reliable recursion needs a business endpoint and a defensive boundary. A tree normally stops at nodes without children, but dirty data can contain cycles, so the query should also record visited identifiers. A depth limit caps damage; it doesn’t replace cycle detection. Tenant and authorization constraints must remain true in both the anchor and every recursive round.

Existence and anti-joins

EXISTS (subquery) cares only whether its subquery returns at least one row. The projected columns and their values don’t matter. A correlated subquery can refer to the current outer row, so NOT EXISTS directly expresses “there is no record matching this row,” which is an anti-join .

NOT IN looks equivalent, but three-valued logic changes its behavior. If the right-hand set contains NULL, comparing a nonmatching value with the whole list can produce UNKNOWN, and WHERE retains only TRUE. For exclusions over a nullable column, NOT EXISTS usually states the intention more clearly.

Examples

These four runnable examples build through windows, staged queries, recursion, and null-safe exclusion logic. The visible shop fixture supplies data for the window, top-orders, and anti-join queries; the recursive example declares its category tree inline. The output came from SQLite 3.45.1; it wasn’t calculated by hand.

CREATE TABLE sales(rep TEXT, sold_on TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('Ari', '2026-08-01', 120),
  ('Ari', '2026-08-02', 180),
  ('Ari', '2026-08-03', 180),
  ('Bo',  '2026-08-01', 90),
  ('Bo',  '2026-08-02', 140),
  ('Bo',  '2026-08-03', 110);

CREATE TABLE orders(
  order_id INTEGER PRIMARY KEY,
  customer TEXT,
  ordered_on TEXT,
  amount INTEGER
);
INSERT INTO orders VALUES
  (1, 'Acme', '2026-08-01', 90),
  (2, 'Acme', '2026-08-03', 150),
  (3, 'Acme', '2026-08-04', 120),
  (4, 'Nova', '2026-08-01', 200),
  (5, 'Nova', '2026-08-02', 80),
  (6, 'Nova', '2026-08-05', 140);

CREATE TABLE accounts(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE suspensions(account_id INTEGER);
INSERT INTO accounts VALUES
  (1, 'Ari'),
  (2, 'Bo'),
  (3, 'Cy');
INSERT INTO suspensions VALUES (2), (NULL);

Numbering, tied ranks, and running values

The first query calculates three results independently for each sales representative. Numbering uses the date as a second ordering key, so equal amounts still have a deterministic order. The cumulative amount uses an explicit ROWS frame ordered by sale date.

Ari’s two 180 rows share amount_rank = 1, but their row_no values are 1 and 2. Displaying the final result in date order doesn’t change the numbers already calculated by the amount-ordered window.

running_amount uses a different order from the rankings because every window function may have its own OVER clause. Putting several analytical values in one query doesn’t force them to share partitions, orders, or frames.

Filtering a window result through a CTE

The next query takes the two largest orders for each customer. It computes position inside ranked_orders, after which the outer query can legally use the alias in WHERE.

WITH ranked_orders AS (
  SELECT
    order_id,
    customer,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer
      ORDER BY amount DESC, order_id
    ) AS position
  FROM orders
  WHERE ordered_on >= '2026-08-01'
)
SELECT customer, order_id, amount
FROM ranked_orders
WHERE position <= 2
ORDER BY customer, position;

customer | order_id | amount
---------+----------+-------
Acme     | 2        | 150
Acme     | 3        | 120
Nova     | 4        | 200
Nova     | 6        | 140

order_id is the stable final ordering key. Even when two orders have equal amounts, the query still determines which one receives the smaller position, and tests don’t depend on the scan order the database happens to choose.

If the requirement is “the top two distinct amounts, including ties,” this query should use DENSE_RANK() instead. Before choosing a ranking function, define whether “top two” means two rows, two competition positions, or two distinct value levels.

Traversing a hierarchy with cycle defenses

The recursive example walks down from a root category. Its path uses delimited identifiers, which avoids treating identifier 1 as already present in 11. A depth limit provides a second boundary for malformed data.

CREATE TABLE categories(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  name TEXT
);
INSERT INTO categories VALUES
  (1, NULL, 'Store'),
  (2, 1, 'Data'),
  (3, 1, 'Tools'),
  (4, 2, 'SQL'),
  (5, 2, 'Python'),
  (6, 4, 'Window functions');

WITH RECURSIVE category_tree(id, name, depth, path) AS (
  SELECT id, name, 0, printf('/%d/', id)
  FROM categories
  WHERE id = 1

  UNION ALL

  SELECT c.id, c.name, t.depth + 1, t.path || c.id || '/'
  FROM categories AS c
  JOIN category_tree AS t ON c.parent_id = t.id
  WHERE t.depth < 10
    AND instr(t.path, printf('/%d/', c.id)) = 0
)
SELECT depth, name
FROM category_tree
ORDER BY depth, id;

depth | name
------+-----------------
0     | Store
1     | Data
1     | Tools
2     | SQL
2     | Python
3     | Window functions

The anchor selects only id = 1. On each round, the recursive member joins children of the current level and appends the new identifier to the path. A branch stops expanding when that identifier is already in its path.

The path functions and string concatenation here are SQLite syntax. A production system should also prevent cycles from entering the data with foreign keys, uniqueness constraints, or write-time validation rather than relying on every read query to defend itself.

Excluding a nullable relation with NOT EXISTS

The final example deliberately puts NULL in the suspension list and combines the results of NOT IN and NOT EXISTS. The NOT IN branch returns no rows. Only the anti-join branch keeps accounts without a suspension.

WITH methods(method, account_id, account_name) AS (
  SELECT 'NOT IN', a.id, a.name
  FROM accounts AS a
  WHERE a.id NOT IN (
    SELECT account_id FROM suspensions
  )

  UNION ALL

  SELECT 'NOT EXISTS', a.id, a.name
  FROM accounts AS a
  WHERE NOT EXISTS (
    SELECT 1
    FROM suspensions AS s
    WHERE s.account_id = a.id
  )
)
SELECT method, account_id, account_name
FROM methods
ORDER BY method, account_id;

method     | account_id | account_name
-----------+------------+-------------
NOT EXISTS | 1          | Ari
NOT EXISTS | 3          | Cy

For account 1, 1 <> 2 is true, but 1 <> NULL is UNKNOWN. The complete NOT IN condition can’t become TRUE. NOT EXISTS searches for an equality match per outer row; NULL = 1 isn’t a match, so it correctly retains Ari and Cy.

If the business treats NULL as another explicit category, put that rule in the data model or filter. Don’t rely on a query author remembering that the right-hand column currently “shouldn’t contain nulls.”

Pitfalls

Fix: Add a unique, meaningful stable key at the end of the window ORDER BY, such as created_at DESC, order_id DESC. If the requirement preserves ties, use RANK() or DENSE_RANK() instead of secretly breaking them with an arbitrary key.

Fix: Spell out ROWS BETWEEN ..., GROUPS BETWEEN ..., or a supported RANGE boundary for cumulative and moving calculations. Test repeated ordering values at the current row instead of testing only data where every value is unique.

Fix: Calculate the window value in a subquery or CTE, then filter in an outer query. Databases with QUALIFY can use that clause, but SQLite 3.45.1 doesn’t support it, so portable code can’t assume it exists.

Fix: Maintain a visited path with unambiguous delimiters, and either stop or report a branch when a node repeats. Keep the depth limit as resource protection, and enforce or validate acyclic data at write time.

Fix: Use NOT EXISTS for a correlated exclusion. If NOT IN is required, explicitly remove NULL inside the subquery and add a regression test whose right-hand set contains a null.

Fix: Write the correct relational query first, then inspect its behavior with the target database’s execution plan. Use product-specific materialization hints, temporary tables, or indexes only after measurements show a real problem.

Further reading

• SQLite window functions
• SQLite WITH clause
• SQLite SELECT documentation
• PostgreSQL window functions tutorial