Subqueries, CTEs, Views, Indexes & JSON

Everything here builds on the basics: nesting queries, naming them, persisting them, making them fast, and storing documents inside relational tables.

Subqueries

A subquery is a SELECT inside another statement. Where it appears determines what it must return.

Scalar subqueries

Must return exactly one row and one column. Usable anywhere a single value is.

SELECT title, price,
       price - (SELECT AVG(price) FROM books) AS vs_average
FROM books;

SELECT * FROM books WHERE price = (SELECT MAX(price) FROM books);

Caution

If a scalar subquery returns more than one row, PostgreSQL raises more than one row returned by a subquery used as an expression. SQLite silently takes the first row. Another case where SQLite hides a bug that PostgreSQL surfaces.

Zero rows yields NULL in both, which then propagates through your comparison as unknown.

Subqueries in FROM (derived tables)

SELECT author_id, book_count
FROM (
  SELECT author_id, COUNT(*) AS book_count FROM books GROUP BY author_id
) AS counts
WHERE book_count > 1;

PostgreSQL requires an alias on a subquery in FROM; SQLite does not. Always write one.

IN / ANY / ALL

SELECT * FROM books WHERE author_id IN (SELECT id FROM authors WHERE birth_year < 1950);

-- PostgreSQL only: the quantified forms, which SQLite lacks
SELECT * FROM books WHERE author_id = ANY (SELECT id FROM authors);       -- same as IN
SELECT * FROM books WHERE price > ALL (SELECT price FROM books WHERE year < 1980);

Correlated subqueries and EXISTS

A correlated subquery references a column from the outer query, so it is conceptually re-evaluated per outer row (planners usually rewrite it into a join).

SELECT a.name,
       (SELECT COUNT(*) FROM books b WHERE b.author_id = a.id) AS book_count
FROM authors a;

EXISTS (subquery) is true if the subquery returns at least one row. It stops at the first match, which makes it the fastest way to ask “does a related row exist?”.

SELECT a.name FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id AND b.year > 1990);

SELECT a.name FROM authors a
WHERE NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id);

SELECT 1 is convention — the select list is ignored entirely, so it can be anything. NOT EXISTS is the null-safe anti-join, unlike NOT IN. See joins.

Common Table Expressions (CTEs)

WITH names a query so you can reference it later in the same statement. It turns a nest of subqueries into a readable, top-down pipeline.

WITH author_stats AS (
  SELECT author_id, COUNT(*) AS books, SUM(price) AS revenue
  FROM books
  GROUP BY author_id
)
SELECT a.name, s.books, s.revenue
FROM author_stats s
JOIN authors a ON a.id = s.author_id
WHERE s.revenue > 20
ORDER BY s.revenue DESC;

Chain multiple CTEs, each able to reference the ones before it:

WITH
recent AS (
  SELECT * FROM books WHERE year >= 1970
),
by_author AS (
  SELECT author_id, COUNT(*) AS n FROM recent GROUP BY author_id
)
SELECT a.name, b.n
FROM by_author b JOIN authors a ON a.id = b.author_id;

A CTE can be referenced more than once in the main query — a subquery cannot.

Materialization

Note

PostgreSQL 12+ inlines a CTE into the main query when it is referenced once and has no side effects, so the planner can optimise across the boundary. Before 12, every CTE was an optimisation fence — always computed separately. Force either behaviour explicitly:

WITH recent AS MATERIALIZED     (SELECT * FROM books WHERE year >= 1970) SELECT ...;
WITH recent AS NOT MATERIALIZED (SELECT * FROM books WHERE year >= 1970) SELECT ...;

SQLite supports the same MATERIALIZED / NOT MATERIALIZED keywords since 3.35.0, and also decides automatically by default.

Use MATERIALIZED when a CTE is expensive and referenced several times; use NOT MATERIALIZED when you want a filter from the outer query to be pushed down into it.

Data-modifying CTEs (PostgreSQL only)

PostgreSQL lets INSERT/UPDATE/DELETE ... RETURNING appear inside WITH, which is how you move rows atomically:

-- PostgreSQL only
WITH deleted AS (
  DELETE FROM orders WHERE qty = 0 RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

All parts see the same snapshot and run in one statement. SQLite does not support this; use two statements inside a transaction.

Recursive CTEs

WITH RECURSIVE lets a CTE reference itself. Both engines require the RECURSIVE keyword and support the same structure:

WITH RECURSIVE name AS (
  <base case>              -- the seed rows
  UNION [ALL]
  <recursive case>         -- references `name`, produces the next rows
)
SELECT ... FROM name;

Execution: run the base case, put its rows in a working set; repeatedly run the recursive case against the working set, appending results, until it produces no new rows.

A number series

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
-- => 1 2 3 4 5 6 7 8 9 10

The WHERE n < 10 is the termination condition. Omit it and the query runs until it exhausts memory or hits SQLITE_MAX limits — there is no automatic depth guard.

Tip

PostgreSQL has generate_series(1, 10) built in, which is faster and clearer for series. SQLite has no such function in the library, though the sqlite3 CLI ships with a compiled-in generate_series table-valued function. For portable SQL, the recursive CTE above works everywhere.

Walking a tree

Using the employees table from joins:

WITH RECURSIVE org AS (
  -- base: the root
  SELECT id, name, manager_id, 0 AS depth, name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive: everyone reporting to someone already in `org`
  SELECT e.id, e.name, e.manager_id, o.depth + 1, o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON o.id = e.manager_id
)
SELECT depth, path FROM org ORDER BY path;

 depth |         path
-------+-----------------------
     0 | Rania
     1 | Rania > Sam
     2 | Rania > Sam > Uma
     1 | Rania > Tariq

Carrying depth and path down the recursion gives you indentation and cycle-safety for free.

Caution

A cycle in the data (A manages B manages A) makes a recursive CTE loop forever with UNION ALL. Two defences:

• Use UNION instead of UNION ALL — it deduplicates, which stops simple cycles.
• Track the path and exclude visited nodes: WHERE o.path NOT LIKE '%' || e.name || '%'.

PostgreSQL 14+ also has CYCLE id SET is_cycle USING path as a built-in cycle detector; SQLite has no equivalent.

Views

A view is a stored query that behaves like a table. It stores no data — it is expanded into the query that references it.

CREATE VIEW book_details AS
SELECT b.id, b.title, b.year, b.price, a.name AS author
FROM books b JOIN authors a ON a.id = b.author_id;

SELECT * FROM book_details WHERE year > 1970;
DROP VIEW book_details;

Views are for encapsulating join logic, presenting a stable interface over a changing schema, and restricting column access.

	SQLite	PostgreSQL
CREATE VIEW / DROP VIEW	Yes	Yes
CREATE VIEW IF NOT EXISTS	Yes	Yes (DROP VIEW IF EXISTS too)
CREATE OR REPLACE VIEW	No — drop and recreate	Yes (column list must stay compatible)
Writable views	No — read-only, use INSTEAD OF triggers	Simple views are auto-updatable (9.3+)
Temporary views	CREATE TEMP VIEW	CREATE TEMP VIEW
Materialized views	No	Yes

An “auto-updatable” PostgreSQL view is one over a single table with no aggregates, DISTINCT, GROUP BY, set operations, or window functions — you can INSERT/UPDATE/DELETE through it directly. Add WITH CHECK OPTION to reject writes that would fall outside the view.

Materialized views (PostgreSQL only)

A materialized view stores its results on disk. Reads are as fast as a table; the data is stale until refreshed.

CREATE MATERIALIZED VIEW author_revenue AS
SELECT a.id, a.name, COUNT(b.id) AS books, COALESCE(SUM(b.price), 0) AS revenue
FROM authors a LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id, a.name;

REFRESH MATERIALIZED VIEW author_revenue;

REFRESH takes an exclusive lock by default. To keep the view readable during the rebuild:

CREATE UNIQUE INDEX ON author_revenue (id);          -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY author_revenue;

The SQLite equivalent is a real table you rebuild yourself, typically inside a transaction.

Indexes

An index is a separate, sorted data structure that lets the engine find rows without scanning the table. Both engines default to B-tree indexes, which support equality, range comparisons, IN, sorting, and prefix LIKE.

CREATE INDEX idx_books_author ON books (author_id);
CREATE UNIQUE INDEX idx_customers_email ON customers (email);
DROP INDEX idx_books_author;      -- same in both engines

Note

PRIMARY KEY and UNIQUE constraints create indexes automatically in both engines. Foreign keys do not. An unindexed FK column makes joins and cascading deletes slow — index every foreign key column yourself.

Composite indexes and the leftmost-prefix rule

A composite index sorts by the first column, then the second, and so on.

CREATE INDEX idx_books_author_year ON books (author_id, year);

This index serves:

• WHERE author_id = 1
• WHERE author_id = 1 AND year = 1974
• WHERE author_id = 1 ORDER BY year

but not WHERE year = 1974 alone — the index is sorted by author_id first, so year values are scattered.

Order the columns by: equality predicates first, then the range or sort column. Put the most selective equality column first when several are equally usable.

Partial indexes

Index only the rows you actually query. Smaller index, cheaper writes. Supported in PostgreSQL and SQLite 3.8.0+.

CREATE INDEX idx_active_orders ON orders (created_at) WHERE status = 'pending';

The planner uses it only when it can prove the query’s WHERE implies the index’s WHERE.

A partial unique index enforces conditional uniqueness — one active subscription per user, unlimited cancelled ones:

CREATE UNIQUE INDEX one_active ON subscriptions (user_id) WHERE status = 'active';

Expression indexes

Index the result of an expression. Supported in PostgreSQL and SQLite 3.9.0+.

CREATE INDEX idx_lower_email ON customers (LOWER(email));
SELECT * FROM customers WHERE LOWER(email) = 'a@example.com';   -- uses the index

The query must use the same expression as the index for it to apply.

Sargability: what kills an index

A predicate is “sargable” if the engine can use an index for it. These are not:

WHERE LOWER(email) = 'x'          -- function on the column (fix: expression index)
WHERE price + 1 > 10              -- arithmetic on the column (fix: price > 9)
WHERE title LIKE '%sea%'          -- leading wildcard: no B-tree can help
WHERE CAST(id AS TEXT) = '5'      -- implicit or explicit cast
WHERE year_col = '1974'           -- type mismatch (PostgreSQL may still cast the literal)

LIKE 'The %' (no leading wildcard) is sargable.

Caution

In PostgreSQL, a B-tree index in a non-C collation cannot serve LIKE 'prefix%'. Create the index with an operator class:

CREATE INDEX idx_title_prefix ON books (title text_pattern_ops);

SQLite’s LIKE optimisation has its own conditions: the pattern must be a prefix with no leading wildcard, and the column’s collation and case_sensitive_like setting must line up. For substring search, use full-text search — PostgreSQL’s tsvector or SQLite’s FTS5 — not LIKE.

Costs

Indexes are not free. Every INSERT, UPDATE of an indexed column, and DELETE must update every relevant index. A table with ten indexes writes roughly ten times the index work. Index what you query, drop what you don’t.

Indexes also only pay off when they are selective. Filtering a boolean column that is 50% true gains nothing — the engine reads half the table either way and a sequential scan is cheaper.

PostgreSQL-specific index features

Feature	Use
CREATE INDEX CONCURRENTLY	Build without locking writes (slower, cannot run inside a transaction)
INCLUDE (cols) (PG 11+)	Covering index — extra payload columns, not part of the key
USING gin	JSONB, arrays, full-text search
USING gist	Geometry, ranges, nearest-neighbour
USING brin	Very large tables with naturally ordered data (timestamps)
USING hash	Equality only; rarely better than B-tree

SQLite has only B-tree indexes. It achieves the covering-index effect automatically: if every column a query needs is present in the index, it never touches the table (EXPLAIN QUERY PLAN reports USING COVERING INDEX).

Statistics

Both planners rely on statistics that go stale.

ANALYZE;                     -- PostgreSQL: whole database
ANALYZE books;               -- PostgreSQL: one table
VACUUM ANALYZE books;        -- PostgreSQL: reclaim space and re-analyse

ANALYZE;                     -- SQLite: writes sqlite_stat1

PostgreSQL runs autovacuum/autoanalyze automatically. SQLite does not — run ANALYZE after a bulk load or major data change, or the planner will guess.

EXPLAIN

PostgreSQL

EXPLAIN alone shows the planner’s estimate. EXPLAIN (ANALYZE, BUFFERS) actually runs the query and adds real timings, row counts, and page reads:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM books WHERE author_id = 1;

 Index Scan using idx_books_author on books  (cost=0.15..8.17 rows=2 width=44)
                                    (actual time=0.021..0.023 rows=2 loops=1)
   Index Cond: (author_id = 1)
   Buffers: shared hit=3
 Planning Time: 0.104 ms
 Execution Time: 0.041 ms

What to look for:

• Seq Scan on a large table with a selective filter — a missing index.
• Estimated rows far from actual rows — stale statistics; run ANALYZE.
• Nested Loop with a high loops count — often fixed by indexing the inner side.
• Sort with Sort Method: external merge Disk — work_mem is too small.
• Rows Removed by Filter in the thousands — the index isn’t selective enough.

Danger

EXPLAIN ANALYZE executes the statement. On an UPDATE or DELETE it really modifies data. Wrap it in BEGIN; ... ROLLBACK; when analysing a write.

SQLite

Plain EXPLAIN in SQLite dumps virtual-machine bytecode, which is rarely what you want. Use:

EXPLAIN QUERY PLAN
SELECT b.title, a.name FROM books b JOIN authors a ON a.id = b.author_id WHERE b.year > 1970;

QUERY PLAN
|--SCAN b
`--SEARCH a USING INTEGER PRIMARY KEY (rowid=?)

Read it as: SCAN = full table scan, SEARCH ... USING INDEX x = index lookup, USE TEMP B-TREE FOR ORDER BY = a sort the planner couldn’t avoid with an index. There are no cost or timing numbers — SQLite’s planner output is qualitative. Use .timer on in the CLI to measure.

JSON

Both engines can store and query JSON inside a column. The models differ.

PostgreSQL: json vs jsonb

• json keeps the exact text, including whitespace, key order, and duplicate keys. Re-parsed on every access.
• jsonb stores a decomposed binary form. Slightly slower to write, much faster to read, supports indexing, deduplicates keys, and does not preserve key order.

Use jsonb unless you specifically need byte-for-byte fidelity.

CREATE TABLE docs (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body jsonb);

INSERT INTO docs (body) VALUES
  ('{"title": "Kindred", "tags": ["sf", "classic"], "meta": {"pages": 264}}');

Operators:

Operator	Meaning
->	Get object field / array element, as jsonb
->>	Get object field / array element, as text
#> / #>>	Get by path array, as jsonb / as text
@>	Left contains right (jsonb only)
?	Does the top level have this key?
?| / ?&	Any / all of these keys?

SELECT body ->> 'title'                       AS title,
       body -> 'meta' ->> 'pages'             AS pages,
       body #>> '{meta,pages}'                AS pages_again,
       body -> 'tags' ->> 0                   AS first_tag
FROM docs;

SELECT * FROM docs WHERE body @> '{"tags": ["sf"]}';
SELECT * FROM docs WHERE body ? 'meta';

Expand into rows, modify, and index:

SELECT d.id, tag
FROM docs d, jsonb_array_elements_text(d.body -> 'tags') AS tag;

UPDATE docs SET body = jsonb_set(body, '{meta,pages}', '300') WHERE id = 1;
UPDATE docs SET body = body || '{"in_print": true}'::jsonb;    -- merge
UPDATE docs SET body = body - 'in_print';                      -- remove a key

CREATE INDEX idx_docs_body  ON docs USING gin (body);                 -- all operators
CREATE INDEX idx_docs_body2 ON docs USING gin (body jsonb_path_ops);  -- smaller, @> only
CREATE INDEX idx_docs_title ON docs ((body ->> 'title'));             -- B-tree on one field

A GIN index is what makes @> containment queries fast — that capability has no SQLite equivalent. PostgreSQL 12+ also implements SQL/JSON path expressions:

SELECT jsonb_path_query(body, '$.tags[*]') FROM docs;
SELECT * FROM docs WHERE body @@ '$.meta.pages > 200';

SQLite: the JSON functions

SQLite stores JSON as ordinary TEXT and provides functions to operate on it. The JSON functions have been built in by default since 3.38.0; before that they needed the json1 extension.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO docs (body) VALUES
  ('{"title": "Kindred", "tags": ["sf", "classic"], "meta": {"pages": 264}}');

The -> and ->> operators (3.38+) mirror PostgreSQL’s, with one important difference: SQLite’s -> returns JSON text, while ->> returns a plain SQL value.

SELECT body ->> '$.title'              AS title,     -- Kindred
       body ->> '$.meta.pages'         AS pages,     -- 264 (integer)
       body ->  '$.tags'               AS tags,      -- ["sf","classic"] as JSON text
       body ->> '$.tags[0]'            AS first_tag  -- sf
FROM docs;

The function forms work on any version with JSON support:

SELECT json_extract(body, '$.title'),
       json_extract(body, '$.meta.pages'),
       json_array_length(body, '$.tags'),
       json_type(body, '$.meta'),
       json_valid(body)
FROM docs;

Expand with the table-valued functions json_each (one level) and json_tree (recursive), then modify with functions that return a new JSON string — assign the result back:

-- Rows whose tags include 'sf'
SELECT DISTINCT d.id FROM docs d, json_each(d.body, '$.tags') j WHERE j.value = 'sf';

UPDATE docs SET body = json_set(body, '$.meta.pages', 300) WHERE id = 1;
UPDATE docs SET body = json_remove(body, '$.meta');
UPDATE docs SET body = json_insert(body, '$.in_print', 1);   -- only if absent
UPDATE docs SET body = json_patch(body, '{"in_print": 1}');  -- RFC 7396 merge

Indexing is limited to expression indexes on specific paths — there is no GIN equivalent, so SQLite cannot index “any path”:

CREATE INDEX idx_docs_title ON docs (json_extract(body, '$.title'));
SELECT * FROM docs WHERE json_extract(body, '$.title') = 'Kindred';   -- uses it

Note

SQLite 3.45.0 (January 2024) added JSONB, a binary internal format with a parallel set of jsonb_* functions (jsonb_set, jsonb_extract, …). It is faster than text JSON but is a SQLite-specific format, not compatible with PostgreSQL’s jsonb. Do not assume the two are interchangeable.

Portability summary

Task	SQLite	PostgreSQL
Extract as value	body ->> '$.a.b'	body ->> 'a', body #>> '{a,b}'
Path syntax	JSONPath-ish '$.a.b'	Key names and '{a,b}' path arrays
Expand array	json_each(body, '$.tags')	jsonb_array_elements(body -> 'tags')
Containment	none — use json_each + filter	body @> '{"tags":["sf"]}'
General index	none	USING gin
Field index	expression index	expression index

The path syntaxes are incompatible, so JSON queries are one of the least portable areas of SQL. If you expect to migrate, keep queried fields in real columns and use JSON only for opaque payloads.

Key points

• Scalar subqueries returning many rows error in PostgreSQL and silently take the first row in SQLite.
• Prefer EXISTS/NOT EXISTS over IN/NOT IN for correlated existence checks — faster and null-safe.
• CTEs make queries readable and can be referenced multiple times; PostgreSQL 12+ and SQLite inline them unless you write MATERIALIZED.
• Recursive CTEs need a base case, a self-referencing case, and a real termination condition — carry a depth or path column to stop cycles.
• Views encapsulate queries; SQLite views are read-only and it has no materialized views.
• Index foreign keys, order composite index columns equality-first, and remember functions on a column kill index usage unless you build an expression index.
• EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, EXPLAIN QUERY PLAN in SQLite — the former executes the statement.
• JSON works in both, with incompatible path syntax; PostgreSQL’s jsonb + GIN is far more powerful than SQLite’s function-based approach.