Skip to content
Coordinator DB
View as Markdown
View as Markdown

Work with joins ​

This guide joins customers with orders and calculates one summary row per customer. A LEFT JOIN retains a customer even when there are no matching orders.

Prepare the tables ​

sql
CREATE TABLE customers(id INTEGER PRIMARY KEY, name String);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO orders VALUES (10, 1, 5), (11, 1, 7);

Join and aggregate ​

sql
SELECT customer.id, customer.name,
       count(orders.id) AS orders,
       coalesce(sum(orders.amount), 0) AS total
FROM customers AS customer
LEFT JOIN orders ON orders.customer_id = customer.id
GROUP BY customer.id, customer.name
ORDER BY customer.id
FORMAT csv;
text
1,"Ada",2,12
2,"Grace",0,0

Understand unmatched rows ​

Grace's unmatched order fields are NULL. Counting orders.id skips that missing value, whereas count(*) would count the retained joined row. sum returns NULL without a contributing amount, so coalesce supplies zero.

Several matches produce several joined rows. Include the intended key predicates and qualify shared column names. Use INNER JOIN when customers without orders should be excluded.

See JOIN and join checks.