Types of Subqueries in SQLite: A Complete Guide with Examples

If you’ve spent any real time writing SQL, you’ve probably run into a situation where a single query just isn’t enough. Maybe you need to filter results based on the outcome of another query, or you need to compare a row against an aggregated value that only makes sense once you’ve already grouped some data. That’s exactly the kind of problem subqueries were built to solve, and SQLite gives you a surprisingly flexible toolkit for using them.

In this guide, I’ll walk through everything you need to know about subqueries in SQLite — what they are, the different types you’ll encounter, how to write them correctly, and where beginners typically trip up. By the end, you should be comfortable reaching for a subquery whenever a plain query can’t get the job done.

What Is a Subquery?

A subquery, sometimes called an inner query or nested query, is simply a SELECT statement embedded inside another SQL statement. The outer statement — which could be a SELECT, INSERT, UPDATE, or DELETE — uses the result of the subquery to decide what to do next.

Here’s the simplest possible example:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The inner query (SELECT AVG(price) FROM products) runs first, calculates the average price across the entire table, and the outer query then uses that single number to filter rows. Without a subquery, you’d have to run two separate queries and manually plug the result of the first into the second. Subqueries let the database do that work for you, in one shot.

SQLite treats subqueries as a core part of its SQL dialect, and it supports most of the subquery patterns you’d find in larger database systems like PostgreSQL or MySQL, with a few of its own quirks worth knowing about.

Why Subqueries Matter

Before diving into types, it’s worth understanding why you’d reach for a subquery instead of, say, a JOIN or a temporary table.

• They let you express “compare this to some computed value” logic cleanly, without creating extra tables.
• They make queries more readable in cases where a JOIN would create ambiguity or duplicate rows.
• They allow filtering based on aggregate conditions that can’t be expressed in a simple WHERE clause.
• They’re often the natural way to express existence checks — “does this row have any matching records elsewhere?”

That said, subqueries aren’t always the most efficient choice. Depending on how SQLite’s query planner handles them, some subqueries can be slower than an equivalent JOIN, particularly correlated subqueries on large tables. I’ll touch on performance considerations toward the end.

Classifying Subqueries: The Two Main Angles

Subqueries in SQLite can be classified in two overlapping ways:

1. By where they’re placed in the outer query (in the WHERE clause, the FROM clause, or the SELECT list).
2. By how they behave — specifically, whether they’re correlated or non-correlated with the outer query.

Let’s go through both angles in detail, since understanding each helps you pick the right tool for the job.

1. Scalar Subqueries

A scalar subquery returns exactly one column and one row — a single value. It can be used anywhere a single value would normally appear: in a WHERE clause, in the SELECT list, or even as part of an expression.

SELECT name,
       price,
       (SELECT MAX(price) FROM products) AS highest_price
FROM products;

Here, the subquery in the SELECT list returns one value (the maximum price), which then gets attached to every row of the outer query’s result. This is a really handy pattern when you want to compare each row against a single computed benchmark, like showing how far each product’s price is from the top price.

A word of caution: if a subquery you intended to be scalar accidentally returns more than one row, SQLite will throw a runtime error. So it’s good practice to make sure your inner query has appropriate LIMIT 1, aggregate functions, or filtering to guarantee a single row.

2. Row Subqueries

A row subquery returns a single row but potentially multiple columns. These are less commonly used in SQLite compared to scalar subqueries, but they do come up, especially when comparing tuples of values.

SELECT *
FROM orders
WHERE (customer_id, order_date) = (
    SELECT customer_id, MAX(order_date)
    FROM orders
    WHERE customer_id = 101
);

This finds the most recent order for customer 101 by comparing a pair of columns against a pair of values returned by the subquery. SQLite does support this row-value comparison syntax, though it’s worth testing on your specific version since row-value support was added in a relatively recent SQLite release (3.15.0 and later).

3. Table Subqueries (Derived Tables)

A table subquery, often called a derived table, returns multiple rows and multiple columns, and it’s used in place of a table — typically in the FROM clause.

SELECT category, avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_averages
WHERE avg_price > 50;

Here, the inner query builds a temporary result set of average prices per category, and the outer query then filters that result set. This pattern is extremely useful when you need to apply a WHERE condition to an already-aggregated value, since you can’t directly filter on an aggregate in the same query using WHERE (that’s what HAVING is for, but derived tables give you more flexibility, especially for multi-step transformations).

Derived tables are also great for breaking complex logic into digestible steps, almost like defining a temporary view inline.

4. Correlated Subqueries

This is where subqueries get more interesting — and more powerful. A correlated subquery references a column from the outer query, meaning it can’t be run independently; it has to be re-evaluated for every row processed by the outer query.

SELECT p1.name, p1.price, p1.category
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

Notice how the inner query references p1.category, a column from the outer query. This means: for every product, calculate the average price within its own category, then check if that product’s price exceeds the category average. That’s a genuinely useful piece of logic that would be awkward to express any other way without window functions.

Correlated subqueries are powerful but can be slower on large datasets because, conceptually, the inner query runs once per row of the outer query. SQLite’s query planner does try to optimize these where possible, but it’s still worth being mindful of performance on big tables.

5. Subqueries with EXISTS and NOT EXISTS

The EXISTS operator checks whether a subquery returns any rows at all — it doesn’t care about the actual values, just whether at least one row matches.

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

This returns every customer who has placed at least one order. The SELECT 1 inside the subquery is a common convention — since EXISTS only cares about row presence, you don’t need to select any particular column.

NOT EXISTS works the same way but flips the logic:

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

This finds customers who have never placed an order — a classic “find the gap” query. EXISTS-based subqueries are almost always correlated, and they tend to be quite efficient because SQLite can short-circuit as soon as it finds one matching row, rather than needing to count or collect every match.

6. Subqueries with IN and NOT IN

The IN operator checks whether a value appears anywhere in a list returned by a subquery.

SELECT name
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = 1
);

This grabs every product belonging to an active category. It’s a clean, readable alternative to a JOIN when you only care about filtering, not about pulling extra columns from the related table.

NOT IN works in reverse, but it comes with a well-known gotcha: if the subquery’s result includes even a single NULL value, the entire NOT IN comparison can behave unexpectedly and return no rows at all. This trips up a lot of beginners.

-- Risky if category_id can be NULL
SELECT name
FROM products
WHERE category_id NOT IN (
    SELECT id FROM categories WHERE active = 0
);

If any row in categories has a NULL id (which shouldn’t normally happen with a primary key, but can happen with other columns), NOT IN silently returns an empty result set. The safer alternative is almost always NOT EXISTS, which doesn’t have this NULL pitfall.

7. Subqueries with ANY, ALL, and Comparison Operators

SQLite doesn’t support the ANY and ALL keywords directly in the same way some other database systems do, but it’s still worth understanding the concept because you can often replicate it using aggregate functions inside a scalar subquery.

For example, instead of writing WHERE price > ANY (subquery), you’d typically write:

SELECT name
FROM products
WHERE price > (SELECT MIN(price) FROM discontinued_products);

And instead of WHERE price > ALL (subquery), you’d use MAX:

SELECT name
FROM products
WHERE price > (SELECT MAX(price) FROM discontinued_products);

This is a good reminder that SQLite’s SQL dialect, while broad, isn’t identical to every other implementation — always check what’s actually supported rather than assuming feature parity.

8. Non-Correlated Subqueries

To round things out, it’s worth explicitly naming the counterpart to correlated subqueries: a non-correlated subquery is completely self-contained and doesn’t reference anything from the outer query. Most of the examples earlier in this article — the AVG(price) example, the derived table example — are non-correlated. SQLite can typically compute these once, cache the result, and reuse it, which makes them generally faster than their correlated counterparts.

Subqueries in INSERT, UPDATE, and DELETE

It’s easy to think of subqueries as a SELECT-only feature, but they’re just as useful in data-modifying statements.

INSERT with a subquery:

INSERT INTO archived_orders
SELECT * FROM orders WHERE order_date < '2023-01-01';

UPDATE with a subquery:

UPDATE products
SET price = price * 1.1
WHERE category_id = (SELECT id FROM categories WHERE name = 'Electronics');

DELETE with a subquery:

DELETE FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);

This last example removes any customer who has never placed an order — but remember the NOT IN and NULL warning from earlier, so double-check that customer_id in orders can’t be NULL, or use NOT EXISTS instead to be safe.

Common Use Cases

Putting it all together, here are situations where subqueries genuinely shine:

• Filtering rows based on an aggregate computed from the same or a related table.
• Checking for the existence (or absence) of related records.
• Building temporary, on-the-fly result sets for further filtering or joining.
• Comparing each row against a per-group benchmark, like a category average.
• Cleaning up or archiving data based on conditions in another table.

Best Practices When Working with Subqueries

1. Prefer EXISTS over IN for existence checks. It handles NULLs safely and tends to perform better on large datasets.
2. Watch out for NOT IN with nullable columns. Use NOT EXISTS instead unless you’re certain the subquery result can never contain NULL.
3. Keep correlated subqueries as lean as possible. Since they run once per outer row, unnecessary columns or joins inside them add up fast.
4. Use derived tables (table subqueries) to break down complex logic. It’s often easier to read a query broken into a few logical steps than one giant nested WHERE clause.
5. Test scalar subqueries for row count safety. If there’s any chance your subquery could return more than one row, add a LIMIT 1 or wrap it in an aggregate function.
6. Consider indexes on columns used inside subqueries, especially correlated ones, since these are exactly the kind of columns SQLite’s query planner will lean on to avoid full table scans.
7. Compare subqueries against JOINs when performance matters. Sometimes rewriting a subquery as a JOIN (or vice versa) produces a noticeably faster plan — use EXPLAIN QUERY PLAN to check.

A Quick Look at EXPLAIN QUERY PLAN

If you’re ever unsure how SQLite is actually executing your subquery, it’s worth running:

EXPLAIN QUERY PLAN
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

This gives you a readable breakdown of whether SQLite is using an index, doing a full table scan, or materializing a subquery as a temporary table. It won’t turn you into a query-tuning expert overnight, but it’s an invaluable habit to build, especially once your database grows beyond a few thousand rows.

Wrapping Up

Subqueries are one of those SQL features that seem intimidating at first but quickly become second nature once you’ve written a handful of them. SQLite supports scalar, row, and table subqueries, along with correlated and non-correlated variants, and gives you EXISTS, IN, and comparison-based patterns to express almost any filtering logic you can think of.

The real skill isn’t memorizing every type — it’s recognizing which pattern fits the problem in front of you. Need a single comparison value? Scalar subquery. Need to check if related rows exist? EXISTS. Need to filter based on group-level aggregates? Correlated subquery or a derived table. Once you start thinking in those terms, subqueries stop being a scary advanced topic and become just another everyday tool in your SQLite toolbox.