Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 33 ยท Querying

Joins, Subqueries and CTEs

Combine tables in SQLite with INNER, LEFT, RIGHT, FULL and CROSS joins, write subqueries, and build readable queries with WITH and recursive CTEs.

Real questions rarely live in one table. Which customers never ordered? Which books were never sold? Who reports to whom, several levels deep? SQLite answers all of these with the standard tools: joins to combine tables, subqueries to nest one query inside another, and common table expressions (CTEs) to give those subqueries names. If you want the full theory, the SQL tutorial covers joins and CTEs in depth. This lesson focuses on what SQLite supports and where it differs.

The headline for modern SQLite: since version 3.39 it supports RIGHT JOIN and FULL OUTER JOIN, which older versions lacked. Recursive CTEs have been available for much longer, and they are surprisingly powerful in a database this small.

Join types in SQLite

INNER Only rows that match on both sides.
LEFT All left rows, matches or NULLs.
RIGHT All right rows. Needs SQLite 3.39+.
FULL Every row from both. 3.39+.

CROSS JOIN pairs every row with every row.

1 / 2

INNER and LEFT joins

An INNER JOIN (plain JOIN) keeps only rows that have a match on both sides. A LEFT JOIN keeps every row from the left table and fills the right side's columns with NULL when nothing matches. The classic use is finding "things with no related rows".

SQL INNER and LEFT joins ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The third query is the "anti-join" pattern: after the LEFT JOIN, rows where the right side is NULL are exactly the books with no orders: Islands Without Names and Northern Ledger. Use COUNT(o.id), not COUNT(*), in the second query so that a customer without orders counts as 0 rather than 1.

Put filters on the right table in the ON clause when you want to keep unmatched left rows. A WHERE condition on the right table turns a LEFT JOIN back into an inner join, because NULL fails the test.

RIGHT and FULL joins

RIGHT JOIN is a LEFT JOIN with the tables swapped. FULL OUTER JOIN returns matched rows plus the unmatched rows from both sides. The following example builds two small tables so you can see every case at once:

SQL RIGHT and FULL joins

Press Run (or Ctrl + Enter) to see the result here.

The left join shows keys 1, 2 and 3 (key 1 has an empty right side). The right join shows 2, 3 and 4 (key 4 has an empty left side). The full join shows all four keys, with NULL on whichever side lacks a match.

If your application must run on SQLite older than 3.39 (some operating systems still ship old copies), avoid RIGHT and FULL. A RIGHT JOIN can be rewritten by swapping the tables in a LEFT JOIN, and a FULL JOIN as a LEFT JOIN combined with UNION ALL of the right-only rows.

CROSS JOIN, USING and NATURAL

  • CROSS JOIN produces every combination of rows. It is useful for generating grids, such as every employee for every month. In SQLite, writing CROSS JOIN also tells the planner not to reorder the two tables, which expert users sometimes exploit.
  • JOIN ... USING (col) joins on a column that has the same name on both sides and shows it once.
  • NATURAL JOIN joins on all same-named columns. It is dangerous: adding a column named id or created_at to two tables silently changes the join. Prefer explicit ON.

SQLite has no LATERAL joins. When you need "for each row, the latest related row", use a correlated subquery or a window function.

Comma joins versus explicit joins

You will see older code that joins tables by listing them with commas and putting the link in WHERE. This is an implicit join. It means exactly the same as an INNER JOIN ... ON, but it is easier to get wrong: forget the WHERE condition and you silently get a CROSS JOIN with every row paired with every other row.

SQL Implicit and explicit joins give the same rows ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first two queries return the same three rows. The last pair shows that the unlinked version returns books times authors rows, which is why explicit joins are the habit to build: the ON condition sits beside its table, and LEFT JOIN is only possible in the explicit form. A comma join cannot express "keep the unmatched rows".

Combining result sets: UNION, INTERSECT and EXCEPT

A join puts columns side by side. A compound select stacks the rows of two queries on top of each other. Both queries must return the same number of columns; the column names come from the first query.

Operator Result Duplicates
UNION Rows in either query Removed
UNION ALL Rows in either query Kept (faster, no sorting)
INTERSECT Rows in both queries Removed
EXCEPT Rows in the first query but not in the second Removed
SQL UNION, UNION ALL, INTERSECT and EXCEPT

Press Run (or Ctrl + Enter) to see the result here.

Whole rows are compared, so Chen appears in both tables but with different fees: she is not in the INTERSECT and shows up on both sides of the EXCEPT queries. UNION returns six rows because the duplicated Asha, 3000 row collapses to one, while UNION ALL returns nine. EXCEPT is not symmetrical, so swapping the queries gives a different answer.

Rules worth remembering:

  • ORDER BY and LIMIT apply to the whole compound result and go at the very end; the column to sort by must be a name (or position) from the first query.
  • SQLite evaluates a chain of compound operators from left to right, with no operator taking priority over another. SELECT 1 UNION SELECT 2 INTERSECT SELECT 2 is (1 UNION 2) INTERSECT 2 and returns just 2. In databases that give INTERSECT higher precedence the same text returns 1 and 2. Use a subquery in FROM (as above) when you want a different grouping.
  • NULL values are treated as equal to each other when removing duplicates.
  • INTERSECT and EXCEPT are often replaceable by WHERE ... IN (SELECT ...) or NOT EXISTS. Choose the compound form when you are comparing whole rows.

Subqueries

A subquery is a SELECT inside another statement. SQLite supports all the usual placements:

  • Scalar subquery: returns one value and can be used anywhere a value fits.
  • IN (subquery): tests membership.
  • EXISTS (subquery): tests whether any row exists, usually correlated with the outer row.
  • Subquery in FROM: a derived table. SQLite does not require an alias.
SQL Subqueries in four places ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Five books cost more than the average (14.83). Leo and Omar bought book 6. Leo, Omar and Sofia ordered since June, so Grace and Hana appear in the NOT EXISTS result.

Use NOT EXISTS instead of NOT IN when the subquery can return NULL: a single NULL makes x NOT IN (...) unknown for every row, and you get no results at all.

Common table expressions

A CTE names a subquery with WITH, so a long query reads as a sequence of steps from top to bottom. You can define several, and later ones can use earlier ones.

SQL Two named steps ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Omar (43.95), Sofia (43.00) and Grace (40.49) are above the average. SQLite may flatten a CTE into the main query or evaluate it once and store the result. Since 3.35 you can force the choice with WITH name AS MATERIALIZED (...) or AS NOT MATERIALIZED (...); the default is usually right.

Recursive CTEs

A recursive CTE has two parts joined by UNION ALL: an anchor that produces the first rows, and a recursive step that refers to the CTE itself and produces more rows from the previous ones. The loop ends when the step produces no new rows. Always make sure the step has a stopping condition (WHERE x < 5), or the query will run until you stop it.

Generating a series is the simplest use, and a very common one, since SQLite has no generate_series() in its core:

SQL Generating rows

Press Run (or Ctrl + Enter) to see the result here.

The second query produces a calendar that correctly rolls from February into March; combined with a LEFT JOIN it fills in days that have no data. The third prints 0 1 1 2 3 5 8 13 21 34.

The most useful recursive pattern is walking a hierarchy. In the company sample, each employee has a manager_id. This query starts at the CEO (no manager) and follows reporting lines down one level, building a path for sorting and a depth for indenting:

SQL Walking an org chart ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first result lists the CEO and the seven people who report to the CEO directly, indented by depth. The second has no depth limit, so it walks the whole tree: 1 employee at depth 0, 7 at depth 1 and 28 at depth 2, for 36 people.

Use UNION instead of UNION ALL in the recursive CTE if your data might contain cycles (A manages B manages A): UNION discards rows it has already seen, which stops the loop. Also, a self-join is not recursion: to show each employee beside their direct manager you only need JOIN employees AS m ON m.id = e.manager_id.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join types need SQLite 3.39 or newer?

    RIGHT JOIN and FULL OUTER JOIN were added in SQLite 3.39. Older versions had only INNER, LEFT, CROSS and NATURAL.
  2. 2. You want books that have never been ordered. Which pattern finds them?

    The LEFT JOIN keeps unmatched books with NULLs on the order side, and the WHERE keeps only those rows.
  3. 3. Why is NOT EXISTS safer than NOT IN with a subquery?

    With NOT IN, one NULL in the list makes the comparison unknown for every row, so nothing matches. NOT EXISTS has no such trap.
  4. 4. What stops a recursive CTE?

    Recursion ends when the step yields no rows, so write a condition such as WHERE x < 5 that eventually becomes false.

Summary

  • INNER keeps matches, LEFT keeps all left rows, and since 3.39 RIGHT and FULL OUTER complete the set. CROSS pairs everything.
  • The anti-join (LEFT JOIN ... WHERE right.id IS NULL) finds rows without partners; put right-side filters in ON.
  • Prefer explicit ON over NATURAL and over comma joins; SQLite has no LATERAL.
  • UNION and UNION ALL stack rows; INTERSECT keeps rows found in both queries, EXCEPT removes the second query's rows from the first. SQLite evaluates them left to right.
  • Subqueries can be scalar, IN, EXISTS, or derived tables; use NOT EXISTS over NOT IN when NULL is possible.
  • CTEs (WITH) name steps; MATERIALIZED hints exist since 3.35.
  • Recursive CTEs = anchor + UNION ALL + step with a stop condition; they generate series and walk hierarchies.

Next: Window Functions, calculations across related rows without collapsing them.