Correlated Subqueries

Correlated subqueries reference columns from the outer query.

That means the inner query is evaluated per outer row context.

Core Concepts

flowchart LR
  A[Outer row] --> B[Inner query uses outer values]
  B --> C[Condition evaluated]
  C --> D[Row kept or filtered]
  D --> E[Repeat for next outer row]

Query type	Inner query depends on outer row?
Non-correlated subquery	No
Correlated subquery	Yes

Code Examples

-- Setup per-category product prices.
CREATE TABLE products_corr (
  product_id INTEGER PRIMARY KEY,
  category   TEXT NOT NULL,
  name       TEXT NOT NULL,
  price      REAL NOT NULL
);

INSERT INTO products_corr (category, name, price)
VALUES
  ('hardware', 'Mouse', 50.0),
  ('hardware', 'Keyboard', 100.0),
  ('hardware', 'Pro Keyboard', 150.0),
  ('stationery', 'Notebook', 20.0),
  ('stationery', 'Pen Set', 40.0);

Expected output
Table created and five rows inserted.

-- Products priced above their category average.
SELECT p1.product_id, p1.category, p1.name, p1.price
FROM products_corr AS p1
WHERE p1.price > (
  SELECT AVG(p2.price)
  FROM products_corr AS p2
  WHERE p2.category = p1.category
)
ORDER BY p1.category, p1.price DESC;

product_id	category	name	price
3	hardware	Pro Keyboard	150.0
5	stationery	Pen Set	40.0

-- Correlated EXISTS pattern (preview).
SELECT DISTINCT p1.category
FROM products_corr AS p1
WHERE EXISTS (
  SELECT 1
  FROM products_corr AS p2
  WHERE p2.category = p1.category
    AND p2.price >= 140
)
ORDER BY p1.category;

category
hardware

SQLite-Specific Nuances

SQLite Nuance

Correlated subqueries are expressive, but can be heavier than equivalent joins/CTEs on large datasets.

Use them when they improve clarity for row-wise logic.

Common Pitfalls / Best Practices

Pitfall

Forgetting correlation predicates in the inner query and accidentally comparing against global results.

Best Practice

Alias outer and inner tables clearly (p1, p2) and review the correlation condition first.

Quick Challenge

Return stationery products priced above the stationery category average.

View Solution

SELECT p1.name, p1.price
FROM products_corr AS p1
WHERE p1.category = 'stationery'
  AND p1.price > (
    SELECT AVG(p2.price)
    FROM products_corr AS p2
    WHERE p2.category = p1.category
  );

name	price
Pen Set	40.0