Expanding the Dataset: A Comprehensive Guide to SQL Joins and Window Functions

Joins and Window Functions in SQL

Joins and Window Functions in SQL are two concepts that significantly elevate querying skills. These tools allow you to combine datasets and perform advanced calculations without collapsing your data.

PART 1: JOINS

A Join combines rows from two or more tables based on a related column between them (usually a primary key and a foreign key).

Common Types of Joins

1. INNER JOIN
Returns only matching records from both tables.

Customers
customer_id | name
1           | Alice
2           | Bob

Orders
order_id | customer_id | amount
101      | 1           | 500
102      | 3           | 300

Query
SELECT c.name, o.amount
FROM Customers c
INNER JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
Alice | 500

Bob is excluded because he has no matching order.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table, and matching records from the right table.

SELECT c.name, o.amount
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
Alice | 500
Bob   | NULL

Bob appears with NULL because he has no orders.

3. RIGHT JOIN

Returns all records from the right table and matching records from the left table.

Customers
customer_id name
1           Alice
2           Bob
3           Carol

Orders
order_id  customer_id         amount
101        1             500
102        1             200
103        4             300

Query
SELECT 
    c.name,
    o.order_id,
    o.amount
FROM Customers c
RIGHT JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
name    order_id  amount
Alice     101      500
Alice     102      200
NULL      103      300

Orders 101 and 102 match Alice.
Order 103 has customer_id 4 → no match in Customers.
Because it's a RIGHT JOIN, all Orders are kept.
Since there’s no customer for id 4 → name becomes NULL.

4. FULL JOIN

Returns all records when there is a match in either table.

Query
SELECT 
    c.name,
    o.order_id,
    o.amount
FROM Customers c
FULL JOIN Orders o
    ON c.customer_id = o.customer_id;

Result
name      order_id           amount
Alice       101                500
Alice       102                200
Bob           NULL         NULL
Carol       NULL             NULL
NULL        103                300

Alice matches orders.
Bob and Carol have no orders → NULL for order columns.
Order 103 has no customer → NULL for name.
A full join keeps everything from both tables.

PART 2: WINDOW FUNCTIONS

A Window Function performs calculations across a set of rows related to the current row without grouping the result into fewer rows.
While Joins combine different tables, Window Functions perform calculations across a set of rows that are related to the current row. Window functions don't "collapse" your data into a single row; they keep the individual row detail intact while adding an analytical layer.
The secret sauce is the OVER () clause, which defines the "window" of data the function looks at. You can further refine this with:

• PARTITION BY: Breaks the data into logical groups (like buckets).
• ORDER BY: Defines the sequence within those buckets.

Basic Syntax
FUNCTION_NAME() OVER (
    PARTITION BY column
    ORDER BY column
)

Common Window Functions

1. ROW_NUMBER ()

Assigns a unique sequential number to rows.

SELECT 
    name,
    department,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS rank_in_department
FROM Employees;

This ranks employees by salary within each department.

2. RANK()

Similar to ROW_NUMBER, but handles ties differently.
If two employees have the same salary; Both get rank 1, and the next rank becomes 3

3. DENSE_RANK()

Like RANK but without skipping numbers.

4. SUM() as a Window Function

Running totals example:

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

This calculates cumulative sales over time.

5. AVG() Over Partition

SELECT 
    department,
    name,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM Employees;

Each row shows the employee salary and department average without grouping.

Key Difference: GROUP BY(GB) vs Window Function(WF)

GB - Collapses rows, WF - Keeps all rows
GB - One result per group, WF - One result per row
GB - Used for summaries, WF - Used for ranking, running totals, comparisons

Sample Combined Example (Join + Window Function)

SELECT 
    c.customer_name,
    o.order_date,
    o.amount,
    SUM(o.amount) OVER (
        PARTITION BY c.customer_name
        ORDER BY o.order_date
    ) AS customer_running_total
FROM Orders o
JOIN Customers c
    ON o.customer_id = c.customer_id;

• Joins customers to orders
• Calculates cumulative spending per customer
• This is powerful for customer lifetime value analysis.

Conclusion

• Joins merge different tables based on a shared column. They change the width of your data.
• Window Functions calculate values (like rankings or running totals) based on other rows in the same result set. They change the depth of your insight without losing row-level detail.
• Joins are for relationship building; Window Functions are for trend and comparative analysis.
• If Joins are about combining data, Window functions are about analyzing data intelligently.