Quick summary
Summarize this blog with AI
Build multi-table joins from keys and row grain
Once join types are familiar, the next challenge is preserving the intended row grain through several relationships. This PostgreSQL 16 case has customers, orders, and order lines across two tenants. Customer 1 and order 10 occur in both tenants, so joining on the local ID alone is incorrect even though the SQL executes. Use the join-type reference if you first need the difference between INNER and LEFT.
State the keys before the query
The customer key is (tenant_id, customer_id), the order key is (tenant_id, order_id), and the line key adds line_no. Each order belongs to one customer in the same tenant. Each line belongs to one order in the same tenant. The fixture’s amounts are known, finite, nonnegative values in one currency. An absent order means zero activity because this teaching source is complete.
First inspect detail at the line grain
WITH customers(tenant_id,customer_id,name) AS (
VALUES ('A',1,'Ada'),('A',2,'Ben'),('B',1,'Cara')
), orders(tenant_id,order_id,customer_id) AS (
VALUES ('A',10,1),('A',11,1),('B',10,1)
), items(tenant_id,order_id,line_no,amount) AS (
VALUES ('A',10,1,10::numeric),('A',10,2,20),('A',11,1,5),('B',10,1,7)
)
SELECT c.tenant_id,c.customer_id,c.name,o.order_id,i.line_no,i.amount
FROM customers c JOIN orders o
ON o.tenant_id=c.tenant_id AND o.customer_id=c.customer_id
JOIN items i ON i.tenant_id=o.tenant_id AND i.order_id=o.order_id
ORDER BY c.tenant_id,c.customer_id,o.order_id,i.line_no;tenant_id | customer_id | name | order_id | line_no | amount
A | 1 | Ada | 10 | 1 | 10
A | 1 | Ada | 10 | 2 | 20
A | 1 | Ada | 11 | 1 | 5
B | 1 | Cara | 10 | 1 | 7Ada’s name appears three times because the output describes lines. Two of those rows belong to the same order. COUNT(*) on this detail result is therefore a line count, not an order count. Ben has no order and is absent from this INNER JOIN result. Those properties follow from the requested population and grain.
Then aggregate each relationship at the required level
WITH customers(tenant_id,customer_id,name) AS (
VALUES ('A',1,'Ada'),('A',2,'Ben'),('B',1,'Cara')
), orders(tenant_id,order_id,customer_id) AS (
VALUES ('A',10,1),('A',11,1),('B',10,1)
), items(tenant_id,order_id,line_no,amount) AS (
VALUES ('A',10,1,10::numeric),('A',10,2,20),('A',11,1,5),('B',10,1,7)
)
, order_amounts AS (
SELECT o.tenant_id,o.order_id,o.customer_id,COALESCE(SUM(i.amount),0) AS order_amount
FROM orders o LEFT JOIN items i
ON i.tenant_id=o.tenant_id AND i.order_id=o.order_id
GROUP BY o.tenant_id,o.order_id,o.customer_id
), customer_amounts AS (
SELECT tenant_id,customer_id,COUNT(*) AS orders,SUM(order_amount) AS amount
FROM order_amounts GROUP BY tenant_id,customer_id
)
SELECT c.tenant_id,c.customer_id,c.name,
COALESCE(a.orders,0) AS orders,COALESCE(a.amount,0) AS amount
FROM customers c LEFT JOIN customer_amounts a
ON a.tenant_id=c.tenant_id AND a.customer_id=c.customer_id
ORDER BY c.tenant_id,c.customer_id;tenant_id | customer_id | name | orders | amount
A | 1 | Ada | 2 | 35
A | 2 | Ben | 0 | 0
B | 1 | Cara | 1 | 7The first stage produces one row per order, including a zero amount for a line-free order under this explicit policy. The second stage produces one row per customer key. The final join can match at most one summary to each customer. Three orders and four lines reconcile to 42 in total. Ben stays in the customer population with zero orders.
If an order without lines indicates an incomplete load instead of a true zero, keep a line count and flag it rather than applying this zero policy. Likewise, unknown line amounts require a completeness measure. Aggregation does not solve missing-source semantics; it only makes the selected contract visible.
Prove why the incomplete key fails
WITH customers(tenant_id,customer_id,name) AS (
VALUES ('A',1,'Ada'),('A',2,'Ben'),('B',1,'Cara')
), orders(tenant_id,order_id,customer_id) AS (
VALUES ('A',10,1),('A',11,1),('B',10,1)
), items(tenant_id,order_id,line_no,amount) AS (
VALUES ('A',10,1,10::numeric),('A',10,2,20),('A',11,1,5),('B',10,1,7)
)
SELECT c.tenant_id,c.customer_id,COUNT(*) AS matched_orders
FROM customers c JOIN orders o ON o.customer_id=c.customer_id
GROUP BY c.tenant_id,c.customer_id ORDER BY c.tenant_id,c.customer_id;tenant_id | customer_id | matched_orders
A | 1 | 3
B | 1 | 3This intentionally wrong query matches both customers numbered 1 to all three orders. Adding DISTINCT customer_id would conceal the error by discarding identity, and SUM(DISTINCT amount) would remove equal values rather than duplicate relationships. The repair is the complete key. In a tenant application, such a mistake can also expose another tenant’s data; database authorization remains necessary in addition to a correct query.
Qualify columns with aliases and name output fields explicitly. Avoid NATURAL JOIN for this contract: its inferred shared columns can change when the schema gains another same-named attribute. If two independent fact tables are involved, aggregate each one before combining them; see why SQL joins multiply rows for that separate pattern.
Optimize the correct relationship
Inspect join cardinality estimates, actual rows, and repeated work on representative data. A composite index may support the complete tenant/order key, but an index is not a guarantee of a faster query. Small tables or broad scans can legitimately use sequential scans or hash joins. The textual order of joins is not a reliable instruction for the physical algorithm.
Use EXPLAIN for a plan and understand that EXPLAIN ANALYZE executes the statement. Keep tests scoped to an approved reporting or disposable environment. Do not copy SQL Server hints into PostgreSQL, disable planner methods globally, or create indexes on every join column without evidence. See a measured PostgreSQL optimization lab.
Use three checks before accepting the report
The final result has three unique customer keys, including Ben. Its order count is three and its amount is 42. Each source line contributes once. Re-run those checks after adding an order with no lines, another tenant that reuses an ID, and equal-value lines. A query that passes only on globally unique sample IDs has not tested the composite key.
FAQ
Why not group by name? Names can repeat and change; use identity keys. Can a correct detail join repeat an order? Yes, one row per line repeats order attributes. Does a foreign key replace the tenant condition? No; the query must use the relationship it intends. Primary reference: joins and grouped table expressions.
