SQLite Subqueries

A subquery is a SELECT statement nested inside another SQL statement. The inner query runs first, and its result is used by the outer query. Subqueries let you build complex logic step by step, making queries that would otherwise require temporary tables or multiple steps.

Subquery Anatomy

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
               └──────────────────────────────┘
                         subquery
                  runs first, returns a value
                  outer query uses that value

Execution order:
  1. Inner query:  SELECT AVG(price) FROM products → 8.72
  2. Outer query:  WHERE price > 8.72

Sample Tables

products
──────────────────────────────────────────────
id │ name          │ category    │ price
───┼───────────────┼─────────────┼───────
1  │ Notebook      │ Stationery  │ 2.99
2  │ Pen           │ Stationery  │ 0.99
3  │ Backpack      │ Bags        │ 24.99
4  │ Water Bottle  │ Accessories │ 8.50
5  │ Laptop Bag    │ Bags        │ 34.99

orders
──────────────────────────────────────────────
id │ product_id │ customer │ qty
───┼────────────┼──────────┼─────
1  │ 3          │ Alice    │ 1
2  │ 1          │ Bob      │ 3
3  │ 3          │ Carol    │ 2
4  │ 5          │ Alice    │ 1

Subquery in WHERE — Scalar

A scalar subquery returns exactly one value (one row, one column). Use it in WHERE comparisons.

-- Products priced above the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

AVG = (2.99+0.99+24.99+8.50+34.99)/5 = 14.49

name        │ price
────────────┼───────
Backpack    │ 24.99
Laptop Bag  │ 34.99

Subquery in WHERE — Using IN

A subquery that returns multiple rows works with IN.

-- Products that have been ordered at least once
SELECT name
FROM products
WHERE id IN (SELECT product_id FROM orders);

product_ids in orders: 3, 1, 3, 5

name
──────────
Notebook    ← id=1 in orders
Backpack    ← id=3 in orders
Laptop Bag  ← id=5 in orders

Subquery with NOT IN

-- Products that have never been ordered
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM orders);

name
──────────────
Pen
Water Bottle

Subquery in SELECT (Column Subquery)

A subquery in the SELECT list adds a computed column to each row. It must return exactly one value per outer row.

-- Show each product with the average price for comparison
SELECT name,
       price,
       (SELECT AVG(price) FROM products) AS avg_price,
       price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

name          │ price │ avg_price │ diff_from_avg
──────────────┼───────┼───────────┼──────────────
Notebook      │ 2.99  │ 14.49     │ -11.50
Pen           │ 0.99  │ 14.49     │ -13.50
Backpack      │ 24.99 │ 14.49     │ 10.50
Water Bottle  │ 8.50  │ 14.49     │ -5.99
Laptop Bag    │ 34.99 │ 14.49     │ 20.50

Subquery in FROM (Derived Table)

A subquery in the FROM clause creates a temporary virtual table, also called a derived table or inline view.

-- Find categories whose average price exceeds 10
SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS category_summary
WHERE avg_price > 10;

category │ avg_price
─────────┼──────────
Bags     │ 29.99

Subquery with EXISTS

EXISTS checks whether a subquery returns at least one row. It stops evaluating as soon as the first match is found, making it efficient.

-- Find customers who ordered a Bag category product
SELECT DISTINCT customer
FROM orders o
WHERE EXISTS (
  SELECT 1
  FROM products p
  WHERE p.id = o.product_id
    AND p.category = 'Bags'
);

customer
────────
Alice
Carol

Subquery vs JOIN — When to Choose

USE SUBQUERY WHEN:                USE JOIN WHEN:
──────────────────────────────    ──────────────────────────────
Filtering with a dynamic value    You need columns from both tables
Checking existence (EXISTS)       Aggregating across related data
Logic reads more clearly          Performance is critical (JOINs
                                  are often faster on large tables)

Key Takeaways

A subquery is a SELECT inside another SQL statement, surrounded by parentheses
Scalar subqueries return one value; use them in WHERE comparisons
Multi-row subqueries return many values; use them with IN or NOT IN
Subqueries in FROM act as derived tables (virtual temporary tables)
EXISTS is efficient for checking whether related rows exist

Previous lesson

Back to course

Next lesson