Samuel Mwai

Posted on Aug 19

# SQL Joins and Window Functions: A Practical Guide

#database #datascience #sql #tutorial

SQL Joins and Window Functions: A Practical Guide

Introduction

SQL is one of the most important tools for working with data. Whether you are analyzing sales, customers, employees, financial transactions, or business performance, SQL allows you to retrieve, combine, transform, and analyze data stored in relational databases.

Two particularly important SQL concepts are joins and window functions.

Joins allow you to combine information from multiple tables, while window functions allow you to perform calculations across related rows without collapsing the individual rows.

Understanding both concepts is essential for anyone working in data analysis, data science, business intelligence, or database development.

Part 1: SQL Joins

What Is a SQL Join?

A SQL join is used to combine rows from two or more tables based on a related column.

For example, imagine we have two tables:

Customers

customer_id	customer_name	country
1	John	Kenya
2	Mary	Uganda
3	Peter	Tanzania

Orders

order_id	customer_id	amount
101	1	50000
102	2	30000
103	1	20000

The common column is customer_id.

We can use it to connect the two tables:

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

The result would be:

customer_name	order_id	amount
John	101	50000
Mary	102	30000
John	103	20000

The join allows us to combine customer information with order information.

1. INNER JOIN

An INNER JOIN returns only rows that have matching values in both tables.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

If a customer has never placed an order, that customer will not appear in the result.

For example:

Customers          Orders

1 John             1
2 Mary             2
3 Peter

An inner join returns John and Mary because they have matching orders.

When to use INNER JOIN

Use an inner join when you only want records that exist in both tables.

Examples include:

Customers who have placed orders
Employees assigned to departments
Products that have sales
Students enrolled in courses

2. LEFT JOIN

A LEFT JOIN returns every row from the left table and matching rows from the right table.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

The result might be:

customer_name	order_id	amount
John	101	50000
Mary	102	30000
John	103	20000
Peter	NULL	NULL

Peter appears even though he has no order.

This is one of the most useful joins in data analysis because it allows you to find records that don't have matching information.

For example:

SELECT
    c.customer_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This finds customers who have never placed an order.

3. RIGHT JOIN

A RIGHT JOIN returns every row from the right table and matching rows from the left table.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

RIGHT JOIN is less commonly used because the same result can usually be achieved by switching the table order and using a LEFT JOIN.

For example:

FROM orders o
LEFT JOIN customers c

is often easier to read.

4. FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables.

If a row has no match, SQL fills the missing values with NULL.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM customers c
FULL OUTER JOIN orders o
    ON c.customer_id = o.customer_id;

This is useful when you want to identify records that exist in one table but not the other.

For example, you might use it to compare two datasets and find:

Customers missing from another system
Products missing from an inventory database
Transactions that don't match
Data quality problems

5. CROSS JOIN

A CROSS JOIN produces every possible combination of rows from two tables.

Suppose:

Products

Laptop
Phone
Tablet

and:

Countries

Kenya
Uganda
Tanzania

A cross join produces:

Laptop  Kenya
Laptop  Uganda
Laptop  Tanzania
Phone   Kenya
Phone   Uganda
Phone   Tanzania
Tablet  Kenya
Tablet  Uganda
Tablet  Tanzania

SQL:

SELECT
    p.product_name,
    c.country
FROM products p
CROSS JOIN countries c;

If one table has 3 rows and another has 3 rows, the result contains:

3 × 3 = 9 rows

Because cross joins can produce very large results, they should be used carefully.

6. SELF JOIN

A SELF JOIN joins a table to itself.

This is useful when records within the same table are related.

For example, an employee table might contain:

employee_id	employee_name	manager_id
1	John	NULL
2	Mary	1
3	Peter	1

Here, manager_id refers back to employee_id in the same table.

We can use a self join to display each employee and their manager:

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Result:

employee	manager
John	NULL
Mary	John
Peter	John

Part 2: SQL Window Functions

What Is a Window Function?

A window function performs a calculation across a set of related rows while keeping the individual rows in the result.

This is the key difference between window functions and regular aggregate functions.

For example, consider:

employee	department	salary
John	IT	60000
Mary	IT	70000
Peter	Sales	50000
Jane	Sales	80000

A regular aggregation might calculate:

SELECT
    department,
    AVG(salary)
FROM employees
GROUP BY department;

Result:

department	average_salary
IT	65000
Sales	65000

The individual employees disappear from the result.

A window function allows us to calculate the average while keeping every employee:

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_average
FROM employees;

Result:

employee	department	salary	department_average
John	IT	60000	65000
Mary	IT	70000	65000
Peter	Sales	50000	65000
Jane	Sales	80000	65000

This is why window functions are extremely powerful for analytics.

7. The OVER() Clause

Window functions use the OVER() clause.

For example:

AVG(salary) OVER ()

The OVER() clause tells SQL that we want the calculation to operate as a window over the result set.

There are two particularly important components:

PARTITION BY
ORDER BY

8. PARTITION BY

PARTITION BY divides the data into groups for the window calculation.

For example:

AVG(salary) OVER (
    PARTITION BY department
)

This calculates the average salary separately for each department.

It is similar to GROUP BY, but it does not collapse the rows.

Think of it as:

GROUP BY → reduces rows

PARTITION BY → keeps rows

9. ORDER BY in Window Functions

ORDER BY determines the order in which the window function processes rows.

For example:

SUM(sales) OVER (
    ORDER BY sale_date
)

This can be used to calculate a running total.

Suppose we have:

sale_date	sales
Jan 1	100
Jan 2	200
Jan 3	150

Query:

SELECT
    sale_date,
    sales,
    SUM(sales) OVER (
        ORDER BY sale_date
    ) AS running_sales
FROM sales;

Result:

sale_date	sales	running_sales
Jan 1	100	100
Jan 2	200	300
Jan 3	150	450

This is called a running total.

10. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row.

SELECT
    employee,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_number
FROM employees;

Result:

employee	salary	row_number
Jane	80000	1
Mary	70000	2
John	60000	3
Peter	50000	4

You can also partition the results:

ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
)

This ranks employees separately within each department.

11. RANK()

RANK() assigns rankings but gives tied values the same rank.

For example:

employee	salary
Jane	80000
Mary	70000
John	70000
Peter	50000

Query:

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

Result:

employee	salary	salary_rank
Jane	80000	1
Mary	70000	2
John	70000	2
Peter	50000	4

Notice that there is no rank 3 because Mary and John share rank 2.

12. DENSE_RANK()

DENSE_RANK() is similar to RANK(), but it does not skip ranking numbers.

Using the same data:

SELECT
    employee,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Jane	80000	1
Mary	70000	2
John	70000	2
Peter	50000	3

The difference is:

ROW_NUMBER()
1, 2, 3, 4

RANK()
1, 2, 2, 4

DENSE_RANK()
1, 2, 2, 3

13. LAG()

LAG() allows you to access a value from a previous row.

This is extremely useful when analyzing changes over time.

For example:

SELECT
    sale_date,
    sales,
    LAG(sales) OVER (
        ORDER BY sale_date
    ) AS previous_sales
FROM sales;

Result:

sale_date	sales	previous_sales
Jan 1	100	NULL
Jan 2	200	100
Jan 3	150	200

We can then calculate the change:

SELECT
    sale_date,
    sales,
    sales - LAG(sales) OVER (
        ORDER BY sale_date
    ) AS sales_change
FROM sales;

Result:

sale_date	sales	sales_change
Jan 1	100	NULL
Jan 2	200	100
Jan 3	150	-50

This is useful for:

Month-over-month analysis
Year-over-year analysis
Stock analysis
Customer activity
Revenue changes

14. LEAD()

LEAD() does the opposite of LAG().

It allows you to access a value from a future row.

SELECT
    sale_date,
    sales,
    LEAD(sales) OVER (
        ORDER BY sale_date
    ) AS next_sales
FROM sales;

Result:

sale_date	sales	next_sales
Jan 1	100	200
Jan 2	200	150
Jan 3	150	NULL

15. FIRST_VALUE() and LAST_VALUE()

Window functions can also retrieve the first or last value within a window.

For example:

SELECT
    employee,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS highest_salary
FROM employees;

This allows every employee to see the highest salary within their department.

16. Window Functions vs GROUP BY

One of the most important concepts to understand is the difference between GROUP BY and window functions.

GROUP BY

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Produces one row per department.

Window Function

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS average_salary
FROM employees;

Keeps every employee while adding the department average.

In simple terms:

GROUP BY summarizes rows.

Window functions analyze rows without removing them.

17. Combining Joins and Window Functions

Joins and window functions are often used together in real-world data analysis.

Suppose you have:

Customers
Orders

You could first join the tables:

SELECT
    c.customer_name,
    o.order_date,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Then use a window function to rank each customer's orders:

SELECT
    c.customer_name,
    o.order_date,
    o.amount,
    ROW_NUMBER() OVER (
        PARTITION BY c.customer_id
        ORDER BY o.amount DESC
    ) AS order_rank
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Now you can identify the largest order for each customer.

18. Finding the Top Record per Group

One of the most common real-world uses of window functions is finding the top record within each group.

For example, suppose you want the highest-paid employee in every department.

First:

SELECT
    employee,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS rn
FROM employees;

Then use a subquery:

SELECT *
FROM (
    SELECT
        employee,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

This returns the highest-paid employee from each department.

19. Real-World Applications

SQL joins and window functions are widely used in data analytics.

Sales Analysis

You can use joins to combine:

Customers
Products
Orders
Sales

Then use window functions to calculate:

Customer rankings
Running revenue
Previous month's sales
Top products
Sales growth

Financial Analysis

Window functions can be used for:

Running balances
Transaction rankings
Month-over-month changes
Portfolio performance
Cumulative revenue

Human Resources

You can calculate:

Employee salary rankings
Department averages
Highest-paid employees
Employee salary differences
Hiring trends

Customer Analytics

You can analyze:

First purchase
Most recent purchase
Customer order rankings
Previous purchases
Customer spending trends

20. Best Practices

When working with joins and window functions, several practices can make your SQL easier to understand and more reliable.

Use table aliases

Instead of:

customers.customer_name

use:

c.customer_name

after defining:

FROM customers c

Always specify the join condition

For example:

ON c.customer_id = o.customer_id

Avoid accidentally creating a Cartesian product unless you intentionally need a CROSS JOIN.

Understand your keys

Before joining tables, determine whether the join key is unique.

Joining a one-to-many table incorrectly can create duplicate rows and inflate totals.

Use meaningful window ordering

For example:

ORDER BY sale_date

is meaningful for a running sales calculation.

Use PARTITION BY when analysis needs groups

For example:

PARTITION BY customer_id

allows calculations to restart for every customer.

Conclusion

SQL joins and window functions are essential skills for data analysts and data scientists.

Joins allow you to bring information from different tables together, while window functions allow you to analyze related rows while preserving the original rows.

The most important joins to understand are:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN

The most useful window functions include:

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
SUM() OVER()
AVG() OVER()
FIRST_VALUE()
LAST_VALUE()

A simple way to remember the concepts is:

Joins combine tables. Window functions analyze rows.

Once you understand these two concepts, you can solve much more advanced SQL problems, from identifying top-performing customers to calculating running totals and comparing current performance with previous periods.