Mastering Joins and Window Functions in SQL

Introduction.

If you’re working with data — especially in tools like Power BI, Excel, or databases like MySQL or PostgreSQL — understanding Joins and Window Functions is a game changer. These two concepts help you combine data and analyze it in powerful ways without losing detail. Here is an article to guide you through joins and window functions.

🔗What Are Joins?

A Join combines rows from two or more tables based on a related column.

Importance of joins.
Most databases are structured into multiple tables to avoid duplication. To analyze the data meaningfully, you need to bring those tables together.

Types of Joins
1️⃣ INNER JOIN
Returns only the matching records in both tables.

Example;

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;`

Meaning: Only customers who have placed orders will appear.

2️⃣ LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and matching records from the right table.
Example;

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Meaning: All customers appear — even those who never placed orders (their order columns will be NULL).

3️⃣ RIGHT JOIN
Returns all records from the right table and matching records from the left.
Example;

SELECT customers.name, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

4️⃣ FULL OUTER JOIN
Returns all records when there is a match in either table.
Example;

SELECT customers.name, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

🧠 Summary
INNER JOIN → Only matching rows

LEFT JOIN → Everything on the left + matches

RIGHT JOIN → Everything on the right + matches

FULL JOIN→ Everything from both

🪟 What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into a single row like GROUP BY does.

Basic Structure of a Window Function

SELECT 
    column,
    window_function() OVER (
        PARTITION BY column
        ORDER BY column
    )

FROM table;
Key Parts:

OVER() → defines the window

PARTITION BY → divides data into groups

ORDER BY → defines order within the group

Common Window Functions
1️⃣ ROW_NUMBER()
Assigns a unique number to each row.

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_position
FROM employees;

Use case: Ranking employees by salary.

2️⃣ RANK()
Similar to ROW_NUMBER but allows ties.

SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

3️⃣ DENSE_RANK()
Like RANK but doesn’t skip numbers after ties.

SELECT 
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

4️⃣ SUM() OVER()
Calculates running totals or grouped totals without grouping the table.

SELECT 
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees;

This keeps every row but adds department totals.

5️⃣ Running Total

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

🧠 summary

• They calculate across related rows.
• They do not reduce rows.
• They add analytical power without losing detail.
• Perfect for ranking, running totals, comparisons.

Window functions increase insight, not width or height.

🗣️ Insights
Think of Joins as connecting puzzle pieces. Each table is a piece of information. When you join them, you build the full picture.
Think of Window Functions as giving each row awareness of its neighbors. Instead of summarizing the table, you let each row “see” the other rows around it.

Joins = relationship builders

Window functions = insight enhancers

Together, they form the backbone of advanced SQL analytics.

Visualization;