Subqueries

A subquery is a query nested inside another query.

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

Core Concepts

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