SQL is the backbone of data manipulation in relational databases, and two of its most essential features are joins and window functions. Joins allow you to combine data from multiple tables, while window functions enable advanced calculations across rows without collapsing them into aggregates. Whether you're a beginner or an experienced data analyst, understanding these concepts can significantly enhance your querying skills. In this article, we'll dive deep into both, with explanations, examples, and tips. What Are SQL Joins? Joins are used to retrieve data from two or more tables based on a related column between them. Let's imagine a small e-commerce database with two tables: -- customers + ----+---------+ | id | name | + ----+---------+ | 1 | Aisha | | 2 | Brian | | 3 | Carla | + ----+---------+ -- orders + ----+-------------+--------+ | id | customer_id | amount | + ----+-------------+--------+ | 1 | 1 | 250 | | 2 | 1 | 100 | | 3 | 2 | 400 | + ----+-------------+--------+ Notice Carla (id 3) has no orders yet. Keep that in mind: it matters a lot once we get to joins. Part 1: SQL Joins A join combines rows from two or more tables based on a related column, in our case customer_id . INNER JOIN Returns only rows that match in both tables. SELECT customers . name , orders . amount FROM customers INNER JOIN orders ON customers . id = orders . customer_id ; Result: Aisha | 250 Aisha | 100 Brian | 400 Carla disappears entirely because she has no matching order. This is the most common join, but it silently drops unmatched rows, a frequent source of "wait, why is my data missing?" bugs. LEFT JOIN Returns all rows from the left table , plus matches from the right table. Unmatched rows get NULL . SELECT customers . name , orders . amount FROM customers LEFT JOIN orders ON customers . id = orders . customer_id ; Result: Aisha | 250 Aisha | 100 Brian | 400 Carla | NULL Now Carla shows up, with NULL for amount . Use LEFT JOIN whenever you need to keep every record from your "main" table, matched data or not, e.g. "show me all customers, even ones who haven't ordered." RIGHT JOIN The mirror image of LEFT JOIN , it keeps all rows from the right table instead. It's less commonly used in practice; most people just flip the table order and use LEFT JOIN instead, since it's easier to read. FULL OUTER JOIN (MySQL workaround) MySQL doesn't support FULL OUTER JOIN natively. You simulate it with UNION : SELECT customers . name , orders . amount FROM customers LEFT JOIN orders ON customers . id = orders . customer_id UNION SELECT customers . name , orders . amount FROM customers RIGHT JOIN orders ON customers . id = orders . customer_id ; This gives you every row from both tables, matched where possible. Quick rule of thumb: if you're not sure which join to use, ask "do I want to keep unmatched rows, and from which table?" That answer picks your join for you. Part 2: Window Functions Joins combine tables. Window functions let you do calculations across a set of rows , like running totals or rankings, without collapsing them into a single row (which is what GROUP BY does). The basic shape: SOME_FUNCTION () OVER ( PARTITION BY column ORDER BY column ) PARTITION BY splits your data into groups (like GROUP BY , but without merging rows). ORDER BY decides the order within each group, which matters for ranking and running totals. ROW_NUMBER() Assigns a unique, sequential number to each row within a partition. SELECT customer_id , amount , ROW_NUMBER () OVER ( PARTITION BY customer_id ORDER BY amount DESC ) AS row_num FROM orders ; This numbers each customer's orders from biggest to smallest, handy for finding "each customer's top order." RANK() and DENSE_RANK() Similar to ROW_NUMBER() , but they handle ties differently: RANK() : ties share a rank, but leaves a gap afterward (1, 1, 3). DENSE_RANK() : ties share a rank, no gap (1, 1, 2). SELECT customer_id , amount , RANK () OVER ( ORDER BY amount DESC ) AS overall_rank FROM orders ; Running Totals with SUM() SELECT customer_id , amount , SUM ( amount ) OVER ( PARTITION BY customer_id ORDER BY id ) AS running_total FROM orders ; For Aisha, this gives 250, then 350 (250+100), a running total per customer, without needing a self-join or subquery. Comparing Rows with LAG() and LEAD() LAG() looks at the previous row; LEAD() looks at the next row, great for comparing an order to the one before it. SELECT customer_id , amount , LAG ( amount ) OVER ( PARTITION BY customer_id ORDER BY id ) AS previous_amount FROM orders ; Joins vs. Window Functions: When to Use Which Use case Tool Combining data from two+ tables JOIN Keeping all rows from one table regardless of matches LEFT JOIN Ranking rows within groups ROW_NUMBER() / RANK() Running totals, moving averages Window function with SUM()/AVG() Comparing a row to the previous/next one LAG() / LEAD() Collapsing rows into one summary row per group GROUP BY (not a window function) The key mental model: joins add columns from other tables; window functions add calculations across rows you already have, without losing any of them. Wrapping Up Start by getting comfortable with INNER JOIN and LEFT JOIN , since they cover the vast majority of real-world queries. Once those feel natural, window functions like ROW_NUMBER() and running SUM() will feel like a natural next step rather than dark magic. Try rewriting a GROUP BY query you've written before using a window function instead. It's one of the fastest ways to make the concept click. Happy querying! 🚀