Subqueries

A subquery is a query nested inside another query.

It helps you compute an intermediate result and reuse it immediately.

Core Concepts

flowchart TD
  A[Inner query runs] --> B[Produces intermediate result]
  B --> C[Outer query consumes result]
  C --> D[Final rows returned]

Subquery location	Typical use
WHERE	Filter using computed values
FROM	Treat subquery as derived table
SELECT	Compute scalar per row/context

Subquery in WHERE

-- Find products priced above global average.
SELECT product_id, name, price
FROM products_sub
WHERE price > (
  SELECT AVG(price)
  FROM products_sub
)
ORDER BY price DESC;

product_id	name	price
3	Pro Keyboard	150.0

Subquery in FROM

-- Aggregate first, then filter in outer query.
SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products_sub
  GROUP BY category
)
WHERE avg_price >= 90
ORDER BY category;

category	avg_price
hardware	100.0

Code Examples

-- Setup table used in examples.
CREATE TABLE products_sub (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      REAL NOT NULL
);

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

Expected output
Table created and four product rows inserted.

-- Scalar subquery in SELECT list.
SELECT
  name,
  price,
  (SELECT AVG(price) FROM products_sub) AS global_avg
FROM products_sub
ORDER BY product_id;

name	price	global_avg
Mouse	50.0	80.0
Keyboard	100.0	80.0
Pro Keyboard	150.0	80.0
Notebook	20.0	80.0

SQLite-Specific Nuances

SQLite Nuance

SQLite supports scalar, table, and nested subqueries in standard SQL positions.

For readability, keep nested levels shallow unless complexity truly requires it.

Common Pitfalls / Best Practices

Pitfall

Writing deeply nested subqueries that are hard to debug and maintain.

Best Practice

If subquery nesting becomes difficult to read, consider rewriting with a CTE (WITH) for clarity.

Quick Challenge

Return products priced below the average price of products_sub.

View Solution

SELECT product_id, name, price
FROM products_sub
WHERE price < (SELECT AVG(price) FROM products_sub)
ORDER BY price;

product_id	name	price
4	Notebook	20.0
1	Mouse	50.0