Subqueries — Inline Selects

~12 min · sql, subqueries, scalar-subquery

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Selects inside selects

A subquery is a SELECT statement nested inside another statement. Three common shapes:

Scalar subquery — returns one value, used as a column or in a comparison: SELECT name, (SELECT count(*) FROM messages WHERE conversation_id = c.id) AS n FROM conversations c;
IN subquery — returns a set, used as the right-hand side of IN.
EXISTS subquery — returns true/false: WHERE EXISTS (SELECT 1 FROM messages m WHERE m.conversation_id = c.id).
Derived table — used in FROM: FROM (SELECT brain, count(*) AS n FROM messages GROUP BY brain) AS sub WHERE sub.n > 100;

Tip: Many subqueries can be rewritten as joins or CTEs and sometimes the rewrite is faster. Use EXPLAIN QUERY PLAN (covered in track 5) to compare. For deeply-nested logic, CTEs (track 8) usually read better than nested subqueries.

Code

Four subquery shapes side by side·sql

-- Scalar subquery as a column
SELECT c.id, c.title,
       (SELECT count(*) FROM messages m WHERE m.conversation_id = c.id) AS n_messages
FROM   conversations c
ORDER  BY n_messages DESC LIMIT 10;

-- IN subquery
SELECT * FROM messages
WHERE  conversation_id IN (
  SELECT id FROM conversations WHERE archived = 0
);

-- EXISTS subquery (often faster than IN for big sets)
SELECT * FROM conversations c
WHERE  EXISTS (SELECT 1 FROM messages m WHERE m.conversation_id = c.id);

-- Derived table in FROM
SELECT day, total FROM (
  SELECT date(created_at) AS day, count(*) AS total
  FROM messages GROUP BY day
) WHERE total > 100;

External links

Exercise

Take a real-ish two-table scenario (conversations + messages, or your own equivalent). Write the same logical query four ways: scalar subquery, IN subquery, EXISTS subquery, and JOIN + GROUP BY. Use EXPLAIN QUERY PLAN to compare. Note which one SQLite picks the simplest plan for and why.

Subqueries — Inline Selects

Selects inside selects

Code

External links

Exercise

Progress

Comments 0