DE Joins and Aggregations
Two SQL techniques power the majority of analytical queries that data engineers and analysts write: joins and aggregations. Joins combine data from multiple tables. Aggregations summarize data across many rows into meaningful totals, averages, and counts. Together, they answer almost every business question a data warehouse needs to handle.
Understanding Joins
Tables in a relational database or data warehouse store related data separately. Customer information lives in the customers table. Order information lives in the orders table. A join connects these tables so a single query can return data from both simultaneously, matching rows that share a common key.
The Puzzle Pieces Analogy
Imagine two puzzle pieces. One piece shows a customer's name and city. The other shows an order amount and date. Neither piece alone tells the full story. A join connects them along their shared edge — the customer ID — to form a complete picture: which customer placed which order, when, and for how much.
Types of Joins
INNER JOIN
Returns only rows that have a matching value in both tables. If a customer has no orders, they do not appear in the result. If an order has no matching customer (data quality issue), it also disappears.
SELECT c.name, c.city, o.order_id, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id; Customers table: Orders table: C001 Sara Delhi ORD01 C001 2500 C002 Tom Mumbai ORD02 C001 1200 C003 Lin Seoul ORD03 C002 800 C004 Ola Lagos (no orders for C003 or C004) INNER JOIN result: Sara Delhi ORD01 2500 Sara Delhi ORD02 1200 Tom Mumbai ORD03 800 (Lin and Ola excluded - no orders)
LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table and matching rows from the right table. If a left-table row has no match, the right-table columns return NULL. This is essential when you need to find customers who have NOT placed any orders.
SELECT c.name, c.city, o.order_id, o.amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id; Result: Sara Delhi ORD01 2500 Sara Delhi ORD02 1200 Tom Mumbai ORD03 800 Lin Seoul NULL NULL <-- included, no orders found Ola Lagos NULL NULL <-- included, no orders found -- Find customers with zero orders: SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
RIGHT JOIN
The mirror of LEFT JOIN. Returns all rows from the right table and matching rows from the left. In practice, engineers rewrite right joins as left joins by swapping the table order — it is easier to read and reason about.
FULL OUTER JOIN
Returns all rows from both tables, matching where possible and filling NULLs where not. Useful for finding records that exist in one table but not the other, in either direction.
-- Find mismatches: orders without customers OR customers without orders
SELECT c.customer_id AS cust_id_from_customers,
o.customer_id AS cust_id_from_orders
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;
CROSS JOIN
Returns every combination of rows from both tables. A table with 100 rows cross-joined with a table of 50 rows produces 5,000 rows. Data engineers use cross joins intentionally when generating all possible combinations — for example, every combination of product and date for a sales forecast grid.
Join Performance Tips
Data engineers write joins that run efficiently on large datasets. Joining on indexed columns runs dramatically faster than joining on unindexed columns. Filtering with WHERE before joining reduces the number of rows the engine must match. Joining on integer keys is faster than joining on long string columns.
Aggregations in Depth
Aggregations transform many rows into fewer summary rows. The GROUP BY clause divides the data into groups; aggregate functions compute one value per group.
Common Aggregate Functions
Function | Purpose | Example ------------|------------------------------|---------------------------------- COUNT(*) | Count all rows | Total number of orders COUNT(col) | Count non-null values | Orders with a shipping date SUM(col) | Total of all values | Total revenue AVG(col) | Mean value | Average order size MAX(col) | Highest value | Largest single order MIN(col) | Lowest value | Earliest order date
Multi-Level Grouping
GROUP BY can group by multiple columns simultaneously, producing one row per unique combination.
-- Revenue by region AND product category
SELECT
s.region,
p.category,
SUM(f.revenue) AS total_revenue,
COUNT(f.order_id) AS order_count,
AVG(f.revenue) AS avg_order_value
FROM fact_sales f
JOIN dim_store s ON f.store_key = s.store_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY s.region, p.category
ORDER BY s.region, total_revenue DESC;
Combining Joins and Aggregations
The most powerful analytical queries join multiple dimension tables to the fact table and then aggregate the results. This pattern forms the backbone of data warehouse reporting.
-- Monthly revenue per customer city and product category
SELECT
d.year,
d.month,
c.city AS customer_city,
p.category AS product_category,
SUM(f.revenue) AS monthly_revenue,
COUNT(*) AS transactions
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.cust_key = c.cust_key
JOIN dim_product p ON f.product_key = p.product_key
WHERE d.year = 2024
GROUP BY d.year, d.month, c.city, p.category
ORDER BY d.year, d.month, monthly_revenue DESC;
ROLLUP and CUBE for Summary Totals
ROLLUP and CUBE extend GROUP BY to automatically produce subtotals and grand totals without writing multiple queries.
-- Revenue by region with subtotals and grand total SELECT region, category, SUM(revenue) FROM sales_summary GROUP BY ROLLUP(region, category); This produces: - Revenue per (region, category) - Subtotal per region (all categories combined) - Grand total (all regions, all categories)
Summary
Joins combine rows from multiple tables based on shared keys, with INNER, LEFT, RIGHT, FULL OUTER, and CROSS variations serving different needs. Aggregations summarize groups of rows using functions like SUM, COUNT, AVG, MAX, and MIN. Combining joins with aggregations across dimension and fact tables is the core SQL pattern that powers data warehouse analytics. Writing these efficiently is one of the most practical and frequently used skills in data engineering.