SQL queries

Querying & inspecting covers viewing rows and the basics of select, and the Querying with joins guide covers joins, group by, and order by. This page documents the rest of the advanced-mode query surface: the features you reach for once a single table is not enough.

Everything here is advanced mode, so switch first with mode advanced (or prefix a single statement with :). The examples use the example library.

DISTINCT

distinct removes duplicate rows from the result. Le Guin wrote two books, but her author_id appears once:

select distinct author_id from books order by author_id

┌───────────┐
│ author_id │
├───────────┤
│         1 │
│         2 │
│         3 │
└───────────┘

distinct also works inside an aggregate — count(distinct author_id) counts the distinct authors.

GROUP BY and HAVING

group by collapses rows that share a value into one row per group, so an aggregate (count, sum, avg, …) summarises each group. having then filters the groups — like where, but applied after grouping. Which authors have written more than one book?

select authors.name, count(*) as books from books
  join authors on books.author_id = authors.author_id
  group by authors.name
  having count(*) > 1

┌───────────────────┬───────┐
│ name              │ books │
├───────────────────┼───────┤
│ Ursula K. Le Guin │ 2     │
└───────────────────┴───────┘

Use where to filter rows before grouping and having to filter groups after — that is the distinction between the two clauses.

Set operations

union, intersect, and except combine the results of two queries that have matching columns. union merges and removes duplicates (union all keeps them); intersect keeps rows in both; except keeps rows in the first but not the second. Everyone associated with the library — authors and members — in one list:

select name from authors
union
select name from members
order by name

┌───────────────────┐
│ name              │
├───────────────────┤
│ Alan Turing       │
│ Grace Hopper      │
│ Italo Calvino     │
│ Jorge Luis Borges │
│ Octavia E. Butler │
│ Ursula K. Le Guin │
└───────────────────┘

Subqueries

A subquery is a select nested inside another statement. Use one with in to test membership — the books written by authors born before 1925:

select title from books
where author_id in (select author_id from authors where birth_year < 1925)

┌──────────────────┐
│ title            │
├──────────────────┤
│ Invisible Cities │
└──────────────────┘

A correlated subquery refers back to the outer query, so it is evaluated per outer row. With not exists, that finds authors who have no books:

select name from authors a
where not exists (select 1 from books b where b.author_id = a.author_id)

┌───────────────────┐
│ name              │
├───────────────────┤
│ Jorge Luis Borges │
└───────────────────┘

Scalar subqueries (one returning a single value) may also appear in a select projection or a where comparison.

Common table expressions (WITH)

A common table expression names a query so the main statement can read from it like a table — useful for breaking a complex query into readable steps. Count each author’s books in a CTE, then join to it for the names:

with book_counts as (
  select author_id, count(*) as n from books group by author_id
)
select authors.name, book_counts.n as books from authors
  join book_counts on authors.author_id = book_counts.author_id
  order by book_counts.n desc, authors.name

┌───────────────────┬───────┐
│ name              │ books │
├───────────────────┼───────┤
│ Ursula K. Le Guin │ 2     │
│ Italo Calvino     │ 1     │
│ Octavia E. Butler │ 1     │
└───────────────────┴───────┘

CTEs can be chained (with a as (…), b as (…)) and may be recursive (with recursive …) for hierarchical data.

Expressions in queries

Anywhere a value is expected — a select projection, where, having — you can write an expression: arithmetic, comparisons, like / in / between / is null, case, cast, and function calls. (This is the same expression grammar used by simple-mode where and by check constraints.)

case chooses a value per row:

select title, case when published < 1970 then 'classic' else 'modern' end as era
from books order by book_id

┌───────────────────────────┬─────────┐
│ title                     │ era     │
├───────────────────────────┼─────────┤
│ A Wizard of Earthsea      │ classic │
│ The Left Hand of Darkness │ classic │
│ Invisible Cities          │ modern  │
│ Kindred                   │ modern  │
└───────────────────────────┴─────────┘

Functions and cast work as you would expect:

select name, length(name) as letters from authors order by author_id

┌───────────────────┬─────────┐
│ name              │ letters │
├───────────────────┼─────────┤
│ Ursula K. Le Guin │ 17      │
│ Italo Calvino     │ 13      │
│ Octavia E. Butler │ 17      │
└───────────────────┴─────────┘

The supported subset

Advanced mode covers a teaching-focused subset of standard SQL — enough to learn real query writing without the full surface of a production database. The query features above, plus joins, order by, and limit/offset, are all available, in select and in insert / update / delete.

Some things are deliberately not available, and will report an error if you try them:

• views and triggers,
• transactions (begin / commit / rollback),
• window functions (… over (…)),
• multiple statements in one command (one statement per line).

To see how any query runs, prefix it with explain (see Querying & inspecting).

See also Querying & inspecting and the Querying with joins guide.