Joins and Aggregations: SQL Patterns for Data Analysis

SQL is the lingua franca of data analysis. Whether you are querying a data warehouse, building a report, or exploring datasets, SQL gets the work done. But SQL queries can get complicated fast, especially when you start combining multiple tables and rolling up data.

This post covers the essential patterns for joins and aggregations that data engineers and analysts use daily. Understanding these patterns deeply will make you faster at writing queries and better at diagnosing issues when they arise.

Understanding Joins

A join combines rows from two or more tables based on a related column. The key insight is that joins are about relationships, and understanding the relationship between tables determines which join type to use.

flowchart LR
    subgraph A["Table A (customers)"]
        A1["id=1 Alice"]
        A2["id=2 Bob"]
        A3["id=3 Carol"]
    end
    subgraph B["Table B (orders)"]
        B1["id=1 Alice → order 101"]
        B2["id=1 Alice → order 102"]
        B3["id=4 Dave (no match)"]
    end
    subgraph IJ["INNER JOIN"]
        IJ1["Alice order 101"]
        IJ2["Alice order 102"]
    end
    subgraph LJ["LEFT JOIN"]
        LJ1["Alice order 101"]
        LJ2["Alice order 102"]
        LJ3["Bob → NULL"]
        LJ4["Carol → NULL"]
    end
    subgraph OJ["FULL OUTER JOIN"]
        OJ1["Alice order 101"]
        OJ2["Alice order 102"]
        OJ3["Bob → NULL"]
        OJ4["Carol → NULL"]
        OJ5["NULL → Dave"]
    end
    A1 --> IJ1
    B1 --> IJ1
    A1 --> LJ1
    B1 --> LJ1
    A2 --> LJ3
    A3 --> LJ4
    A1 --> OJ1
    B1 --> OJ1
    A2 --> OJ3
    A3 --> OJ4
    B3 --> OJ5

Inner Join

An inner join returns only rows that have matches in both tables. If a customer has no orders, they do not appear in the result. The key risk is silent data loss: if either side of the join has unexpected NULLs or missing keys, those rows disappear without warning. No error is raised, and the analyst may not notice the missing data.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

This query returns customers who have placed orders. The customer_id exists in both tables. If 10% of orders have a customer_id that does not exist in the customers table, those 10% of orders vanish from the result. This is the most common join type in analytical queries, but the silent row drop risk makes it dangerous for data quality checks. Always verify key completeness before joining: run a count of NULL or missing keys on both sides first.

Left Join

A left join returns all rows from the left table, with NULL values for the right table when there is no match. The intentional use case is preserving the left table while adding context from the right. The common mistake is using left join when you actually want only matches, then forgetting to filter out the NULLs.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Now every customer appears, even those who have never placed an order. Their order_id and total_amount are NULL. This is useful for finding customers who have not taken a specific action. The NULL values flag the non-matching rows. When you only want customers who have placed orders, add WHERE o.order_id IS NOT NULL after the join. Without this filter, a left join produces the same result as an inner join if every left row has a match, but silently includes non-matching rows if some do not.

Right Join

A right join returns all rows from the right table. This join type is almost always a sign that the tables were written in the wrong order. The fix is to reverse the table order and use a left join instead.

-- Every order appears, even if the customer is somehow missing
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

The rewritten version:

-- Equivalent: flip the tables and use LEFT JOIN
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Right joins are rarely used in practice because they are harder to read and easier to get wrong. If you find yourself reaching for a right join, pause and flip the tables instead. The only situation where a right join is genuinely appropriate is when you are joining to a table you cannot modify (like a view with a fixed order) and the right table must be preserved.

Full Outer Join

A full outer join returns all rows from both tables, with NULLs where there is no match. The practical use case is data quality checks: finding records that exist in one table but not the other. The danger is that NULLs on both sides complicate downstream aggregation.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

This shows all customers and all orders, with NULLs where there is no relationship. Useful for data quality checks to find orphaned records. The problem comes when you aggregate without handling NULLs: SUM(o.total_amount) drops all rows where o.total_amount IS NULL, giving you a lower total than actually exists. Always use COALESCE(o.total_amount, 0) in aggregations over full outer join results. The NULLs represent real data (non-matching rows), not zero values, so treating them as zero in sums is correct.

Cross Join

A cross join produces a Cartesian product: every row from the first table joined with every row from the second table. The danger is row explosion. If you have 100 customers and 50 products, you get 5,000 rows. With 10,000 customers and 1 million product records, you get 10 billion rows.

SELECT
    c.customer_name,
    p.product_name
FROM customers c
CROSS JOIN products p;

Cross joins are occasionally useful for generating combinations. A reporting skeleton that needs one row per customer per month for the past 12 months uses a cross join to generate the 12-month grid before left-joining actual sales data. Without the cross join, the report would only show months where sales exist. They are also used in test data generation. In all other cases, a cross join is almost certainly a mistake. Before running a cross join, always estimate the result size: run SELECT COUNT(*) FROM customers CROSS JOIN products without the SELECT columns to see how many rows you are about to produce.

Multi-Table Joins

Real queries often involve more than two tables. The join order matters for both correctness and performance.

The Logical Flow

When you join three tables, the SQL engine evaluates two tables first, then joins the result to the third. SQL semantics do not guarantee which pair joins first, but the final result is the same regardless of order. The query optimizer chooses the join order based on statistics, and it usually gets this right. The important thing to know is that the optimizer can only choose from the join orders you give it: if your WHERE clause filters aggressively on one table, that table should appear early in the join order so the optimizer can apply the filter before joining.

In the example, the WHERE clause filters orders to 2026 and beyond. The optimizer will typically join orders to customers first (because the filter reduces orders to a small set), then join to order items, then to products. Each join narrows the result set. If you wrote the same query without the date filter, the optimizer might choose a different order, starting with the smallest table or the one with the best indexes.

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01';

The implicit join order is: filter orders by date, then join to customers, then join to order items, then join to products. Each step reduces the row count. Understanding this flow helps you diagnose when a query returns unexpected rows: the problem is usually in the join that produces the multiplication.

Aliases for Readability

Long table names get tedious. Use aliases. The other reason aliases matter is avoiding ambiguous references when the same table is joined multiple times. A query that joins customers to themselves to find parent-child relationships needs two different aliases for the same table.

SELECT
    o.order_id,
    c.customer_name,
    p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id

Aliases like o, c, and p are short but meaningful once you are familiar with the schema. In a four-table join, the aliases also make it obvious which table each column comes from without reading the FROM clause. The common failure is using the same alias for two different tables by mistake, which produces a syntax error that is usually caught. The worse failure is using no aliases with long table names, then accidentally using the wrong table’s column and getting silently wrong results.

Understanding Aggregations

Aggregations summarize data across multiple rows. They transform rows into summary values.

Basic Aggregations

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order_value,
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date
FROM orders;

These scalar aggregations return a single row with summary statistics. The key distinction is COUNT(*) versus COUNT(column): COUNT(*) counts all rows including NULLs, while COUNT(column) counts only rows where that column is not NULL. If total_amount is NULL for some orders, COUNT(*) and COUNT(total_amount) will differ. SUM(total_amount) ignores NULLs, so a row with a NULL amount contributes zero to the sum, not nothing. This is usually what you want, but it means a table with 10 rows where 3 have NULL amounts will sum only the 7 non-NULL values.

GROUP BY

GROUP BY creates groups and returns one row per group. Every column in the SELECT list must either be in the GROUP BY or be an aggregate function. This is the most enforced SQL rule in modern database modes.

SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;

One row per customer, with aggregated order statistics. If a customer has no orders, they do not appear in the result because GROUP BY customer_id groups only rows that exist in the orders table. To include customers with zero orders, you need a left join from customers to orders first.

Multiple Columns in GROUP BY

Group by as many columns as you need. Each unique combination of all grouped columns produces one row.

SELECT
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
ORDER BY customer_id, order_month;

This gives you monthly order counts per customer. The DATE_TRUNC truncates the timestamp to the month, so all orders in the same month group together. The ORDER BY is separate from the GROUP BY and controls the sort order of the result, not the grouping itself. A common mistake is thinking ORDER BY customer_id, order_month will make all rows for the same customer appear together; that is what GROUP BY does. The ORDER BY is only for the presentation order of the grouped rows.

Filtering Aggregates with HAVING

WHERE filters rows before aggregation. HAVING filters groups after aggregation.

SELECT
    customer_id,
    SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 10000;

This finds high-value customers. Only customers with more than $10,000 in total orders appear.

Combining Joins and Aggregations

The real power comes from joining tables, then aggregating the result.

SELECT
    c.customer_name,
    c.customer_region,
    SUM(o.total_amount) AS total_revenue,
    COUNT(o.order_id) AS order_count,
    AVG(o.total_amount) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.customer_name, c.customer_region
ORDER BY total_revenue DESC
LIMIT 20;

This returns the top 20 customers by revenue, with their region and order statistics.

Window Functions

Window functions perform calculations across a set of rows related to the current row. Unlike aggregate functions, they do not collapse rows.

Running Totals

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_sales;

The running total sums all revenue from the beginning through the current row. The window frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is what makes it a running total rather than a grand total. UNBOUNDED PRECEDING means include all rows from the start of the partition, and CURRENT ROW means stop at the current row. Without this frame specification, SUM() OVER() computes the sum of all rows in the partition, which is the same as a grand total.

Rank and Row Number

SELECT
    customer_name,
    lifetime_value,
    ROW_NUMBER() OVER (ORDER BY lifetime_value DESC) AS row_num,
    RANK() OVER (ORDER BY lifetime_value DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY lifetime_value DESC) AS dense_rank
FROM customer_lifetime_values;

The difference between these three is what happens when two rows have the same value:

• ROW_NUMBER assigns sequential numbers regardless of ties. If two customers are tied at $10,000, one gets row_num 1 and the other gets row_num 2, but which one gets which is arbitrary.
• RANK assigns the same rank to ties, then skips the next rank. If two customers tie at rank 1, the next customer gets rank 3.
• DENSE_RANK assigns the same rank to ties without skipping. If two customers tie at rank 1, the next customer gets rank 2.

Use ROW_NUMBER when you need a unique, stable ordering for pagination or deduplication. Use RANK when you are building a leaderboard where ties mean the next rank is skipped. Use DENSE_RANK when ties should not affect the ranking of others, such as in qualification thresholds where the top 10 by score make a cutoff.

Partition By

PARTITION BY restarts the window for each group. Without PARTITION BY, the window function operates over the entire result set. With PARTITION BY, it resets for each group.

SELECT
    customer_name,
    order_month,
    monthly_revenue,
    SUM(monthly_revenue) OVER (
        PARTITION BY customer_name
        ORDER BY order_month
    ) AS cumulative_revenue_per_customer
FROM customer_monthly_revenue;

The running total restarts for each customer. This is the key difference from a simple running total: the PARTITION BY customer_name splits the data into one partition per customer, and the SUM() OVER() and ORDER BY order_month apply independently within each partition. Without PARTITION BY, the running total would span all customers and all months, producing a single cumulative sum across the entire table.

Common Analytical Patterns

Year-over-Year Comparison

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS current_month_revenue,
    LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS previous_month_revenue,
    SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS month_over_month_change
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Percentage of Total

SELECT
    product_category,
    SUM(revenue) AS category_revenue,
    SUM(SUM(revenue)) OVER () AS total_revenue,
    SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS percent_of_total
FROM product_sales
GROUP BY product_category
ORDER BY percent_of_total DESC;

The nested SUM aggregate computes the total, and the window function makes it available to every row.

Moving Averages

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_average
FROM daily_sales;

This computes a 7-day moving average, including the current day and the 6 preceding days.

Join Performance Considerations

Joins can be expensive. A few principles help keep queries fast.

Join on Indexed Columns

Ensure join keys have indexes in both tables. This affects join performance dramatically. A join between two tables where neither has an index on the join key forces the database to do a nested loop scan: for each row in the outer table, scan the entire inner table. On tables with millions of rows, this is catastrophic.

-- Index on foreign key columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

The foreign key columns in the fact table and the primary key columns in the dimension table are the minimum indexes needed. In a star schema, the fact table’s foreign keys should all be indexed. The dimension table’s primary key is typically already indexed. Composite indexes help when you join on multiple columns: an index on (customer_id, order_date) speeds up joins where both columns appear in the join condition.

Filter Early

Apply WHERE clauses before joins when possible. This reduces the number of rows that need to be joined. The database evaluates the WHERE clause before the join, so filtering before the join means fewer rows enter the join operation.

-- Better: filter before join
SELECT c.customer_name, SUM(o.total_amount)
FROM customers c
JOIN (SELECT * FROM orders WHERE order_date >= '2026-01-01') o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

-- Filters all orders first, then joins

The subquery pattern (SELECT * FROM orders WHERE order_date >= '2026-01-01') o materializes the filtered orders before the join, so the join only processes rows that passed the date filter. Some optimizers handle this automatically when you put the WHERE clause on the orders table directly in the main query, but the subquery pattern makes the intent explicit and works reliably across more engines.

Beware of Cross Joins

A cross join with large tables produces enormous result sets. Always verify your join conditions before running. The most common cause is accidentally omitting the ON clause in a JOIN statement, which converts it to a cross join.

-- This is almost certainly wrong if both tables have millions of rows
SELECT * FROM customers CROSS JOIN orders;

The query above is a cross join because there is no ON clause. If you meant to write JOIN orders ON customers.id = orders.customer_id but accidentally wrote JOIN orders, the database treats it as a cross join and produces a Cartesian product. The result is almost certainly wrong, and on large tables it can be enormous. Before running any multi-table join, check that every JOIN has an ON clause. An easy habit is to write the ON condition before you write the SELECT columns.

Common Pitfalls

Ambiguous Column Names

When two tables have columns with the same name, qualify them with table aliases. The error this produces depends on the database mode. In strict SQL modes, the query fails with an ambiguous column error. In permissive modes, it silently picks one, which is worse because the query runs but returns wrong results.

-- WRONG if both tables have customer_id
SELECT customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

-- RIGHT: specify which table
SELECT c.customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

The safest habit is to always qualify every column with its table alias, even when there is no ambiguity. This makes the query self-documenting and prevents silent breakage if a new table is added to the query with a column of the same name.

NULL in Join Conditions

NULL does not equal NULL in SQL. A join on NULL values returns no matches. This is a frequent source of silent data loss in production queries.

-- This may not match rows where region is NULL
SELECT * FROM customers c JOIN regions r ON c.region = r.region;

-- Handle NULLs explicitly if needed
SELECT * FROM customers c
JOIN regions r ON COALESCE(c.region, 'Unknown') = COALESCE(r.region, 'Unknown');

In the first query, a customer with region IS NULL never matches any region row because NULL = NULL evaluates to NULL, not TRUE. All customers without an assigned region disappear from the result silently. The COALESCE approach treats NULL as a known value, so those customers match the ‘Unknown’ region row. The choice depends on whether NULL actually means “unknown” (in which case COALESCE is correct) or whether NULL means “not applicable” (in which case dropping the row is correct). Know which interpretation applies in your data before applying COALESCE blindly.

Aggregation Without GROUP BY

-- WRONG: will fail or return unexpected results
SELECT customer_name, COUNT(*) FROM orders;

-- RIGHT: include all non-aggregated columns in GROUP BY
SELECT customer_name, COUNT(*) FROM orders GROUP BY customer_name;

Modern SQL modes like ONLY_FULL_GROUP_BY reject queries where non-aggregated columns are not in GROUP BY.

When to Use Each Join Type

Join Type	When to Use	Key Risk
INNER JOIN	You only want matching rows from both tables; drop rows with no match	Silent data loss if one side has unexpected NULLs or missing keys
LEFT JOIN	Preserve all left table rows; right table is supplementary context	Right side NULLs can confuse analysts; use COALESCE for display
RIGHT JOIN	Almost never—rewrite as LEFT JOIN with table order reversed	Harder to read and maintain
FULL OUTER JOIN	Union of both tables when rows may exist in only one side	Result can be large; NULLs complicate downstream aggregation
CROSS JOIN	Cartesian product (rarely intentional—test data, grid combinations)	Always verify intent; check row count before running on real tables
SELF JOIN	Hierarchies (employee-manager), same-table comparisons	Complex aliasing; easy to accidentally cross-join

SQL Observability Hooks

Track these metrics to diagnose query performance issues:

-- Identify large scans driving slow joins
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Find missing indexes on foreign keys (tables with high seq_scan but no index on FK column)
SELECT
    t.tablename,
    t.seq_scan,
    i.indexname,
    i.indexdef
FROM pg_stat_user_tables t
LEFT JOIN pg_indexes i ON t.schemaname = i.schemaname
    AND t.tablename = i.tablename
WHERE t.seq_scan > 1000
ORDER BY t.seq_scan DESC;

Log every join query: tables involved, row counts before and after join, execution time. Alert on: queries returning >10x expected rows (possible Cartesian product), missing index warnings, NULL join key counts > 0.

SQL Production Failure Scenarios

Cartesian product explosion

A query joins customers (10,000 rows) to orders (100,000 rows) without a join condition. The result is 1 billion rows. The query runs for 30 minutes consuming memory until the database kills it or returns a disk-full error. The analyst intended WHERE customers.id = orders.customer_id but accidentally used a comma-separated FROM clause.

Mitigation: Always use explicit JOIN syntax with ON conditions. Enable sql_require_full_join_condition in strict SQL modes. Before running ad-hoc joins, estimate result size: COUNT(*) without SELECT columns first.

NULL join key causing silent row drops

Two tables both have region as a VARCHAR column used in the join. Rows where region IS NULL in either table never match because NULL = NULL returns NULL in SQL. A report of orders by region silently drops all orders from regions not yet assigned.

Mitigation: Use COALESCE(column, 'UNKNOWN') or NULLIF(column, '') on join keys to normalize empty strings and NULLs. Add a check query SELECT COUNT(*) FROM t1 WHERE join_key IS NULL before joining and alert if counts are non-zero.

Missing index causing sequential scan on large table

A fact table with 500 million rows has no index on customer_id. A LEFT JOIN to dim_customer (10,000 rows) triggers a sequential scan of the entire fact table. The query takes 45 minutes instead of 5 seconds.

Mitigation: Create indexes on all foreign key columns. Profile query plans with EXPLAIN ANALYZE before deploying. Use pg_stat_user_indexes to find unused indexes and pg_stat_user_tables to find high sequential scan tables.

FULL OUTER JOIN producing unexpected NULLs downstream

A FULL OUTER JOIN between fact_sales and fact_returns produces rows with NULLs on both sides. An analyst runs SUM(amount) without handling NULLs—the NULL rows are silently dropped, producing lower totals than expected.

Mitigation: Use COALESCE(column, 0) around all columns from FULL OUTER JOIN results. Add a validation query comparing row counts and sums to expected ranges before surfacing results to end users.

SQL Joins and Aggregations Quick Recap

• INNER JOIN: only matching rows from both tables; watch for silent data loss.
• LEFT JOIN: preserve all left table rows; use COALESCE to handle NULLs from non-matching right side.
• RIGHT JOIN: rewrite as LEFT JOIN with table order flipped—never use RIGHT JOIN.
• CROSS JOIN: accidental Cartesian products destroy query performance; always verify join conditions.
• FILTER before JOIN when possible to reduce rows that need joining.
• Window functions (ROW_NUMBER, SUM OVER) let you aggregate without collapsing rows.
• Index foreign key columns; profile query plans with EXPLAIN ANALYZE before production.
• NULL in join keys never matches—normalize with COALESCE before joining.

For deeper dives into the data modeling that sits behind these queries, see Kimball Dimensional Modeling for star schema design, or Slowly Changing Dimensions for handling attribute changes over time.

Joins and aggregations are the foundation of analytical SQL. The key concepts:

• INNER JOIN returns matching rows only
• LEFT JOIN preserves left table rows with NULLs for non-matches
• FULL OUTER JOIN shows all rows from both tables
• GROUP BY creates groups for aggregate calculations
• WHERE filters before aggregation; HAVING filters after
• Window functions perform calculations across related rows without collapsing them

These patterns combine to answer complex analytical questions. A typical business intelligence query joins dimension tables to fact tables, filters by date range, groups by business entities, and computes summary statistics.

For deeper dives into the data modeling that sits behind these queries, see Kimball Dimensional Modeling for star schema design, or Slowly Changing Dimensions for handling attribute changes over time.