Joins in PostgreSQL: USING, Aliases and a First Report
All lessons
- Joins in PostgreSQL: USING, Aliases and a First ReportFree
- LEFT JOIN, COALESCE and the FILTER Clause Locked
- DISTINCT ON: The Latest Row per Group Locked
- LATERAL Joins: Top N Rows per Group Locked
- CTEs: Building a Report in Readable Steps Locked
- WITH RECURSIVE: Walking a Category Tree Locked
- generate_series: Reports Without Missing Days Locked
- Arrays: @>, && and ANY Locked
- unnest, array_agg and the Double-Counting Trap Locked
- JSONB: ->, ->>, ? and @> Locked
- JSONB Arrays to Rows: jsonb_to_recordset Locked
- GIN Indexes and Selectivity for JSONB and Arrays Locked
- Window Functions: ROW_NUMBER, RANK, DENSE_RANK Locked
- Running Totals and Window Frames: RANGE vs ROWS Locked
- LAG and LEAD: Month-over-Month Change Locked
- Shares and Top N per Group with Window Aggregates Locked
- Views: Saved Queries That Are Always Current Locked
- Materialized Views: Fast but Stale Locked
- UPSERT with INSERT ... ON CONFLICT Locked
- Final Project: A Customer Report in One Query Locked
You are not signed in — the lab works, but your progress is not saved. Sign up
Read first
The SQL
SELECT c.customer_id, c.name,
SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c USING (customer_id)
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC;
-- variant with a filter:
SELECT SUM(o.amount)
FROM orders AS o
JOIN customers AS c USING (customer_id)
WHERE c.city = 'Basel';`USING (customer_id)` only works when both tables call the key exactly the same. Otherwise write `ON a.x = b.y`.
Loading the lab…