Data EngineeringIntermediate15 min readUpdated March 2025

SQL Joins & Aggregations

Combine data across tables with joins and summarize it with aggregate functions. Understanding join types and grouping semantics is essential for building correct, performant transformations.

Types of Joins

A join combines rows from two tables based on a related column. The join type determines what happens to unmatched rows:

INNER JOIN — Only rows with matches in both tables.
LEFT JOIN — All rows from the left table, with NULLs where the right has no match.
RIGHT JOIN — All rows from the right table, with NULLs where the left has no match.
FULL OUTER JOIN — All rows from both tables, matched where possible.
CROSS JOIN — Every combination of rows (Cartesian product).

Join Examples

Finding customers who have never placed an order is a classic use of LEFT JOIN with an anti-join filter:

sql

-- Inner join: orders with customer info
SELECT o.order_id, c.full_name, o.amount
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id;

-- Anti-join: customers with no orders
SELECT c.customer_id, c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Aggregate Functions and GROUP BY

Aggregates collapse many rows into one value per group. Every non-aggregated column in SELECT must appear in GROUP BY. Use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.

sql

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*)                        AS orders,
  SUM(amount)                     AS revenue,
  AVG(amount)                     AS avg_order_value,
  MAX(amount)                     AS largest_order
FROM orders
WHERE status = 'COMPLETED' GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Join Performance Tips

Joins are often the most expensive part of a query:

Join on indexed or well-distributed key columns.
Filter early — reduce row counts before joining large tables.
Watch for accidental row multiplication when joining on non-unique keys.
Inspect the query plan with EXPLAIN to see join strategy (hash, merge, nested loop).

Key Takeaways

Choose the join type based on how unmatched rows should be treated.
WHERE filters rows; HAVING filters aggregated groups.
Non-unique join keys can silently duplicate rows and inflate metrics.
Use EXPLAIN to understand and tune slow joins.

Contact Us

Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com