Understanding Joins and Window Functions in SQL

Source: Dev.to

Introduction

In this article we explore two of the most powerful and widely‑used features in SQL: JOINs and Window Functions.
We’ll start by defining each concept, then walk through practical examples that show when, where, and why they are used in real‑world scenarios.

1️⃣ JOINs

A JOIN is a clause that combines rows from two or more tables based on a related column.
The purpose of a join is to retrieve data that is spread across multiple tables and present it as a single, unified result set.

Example scenario:
An orders table and a customers table can be joined to answer questions such as “Which customer placed each order?”

Types of Joins

1.1 INNER JOIN

-- INNER JOIN example
SELECT  c.first_name,
        c.last_name
FROM    article.customers   AS c
INNER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: Returns only the records that have a matching customer_id in both customers and orders.

1.2 LEFT JOIN

-- LEFT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
LEFT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: Returns all customers. If a customer has never placed an order, order_date (and other order columns) will be NULL.
The left join is also known as a left outer join.

1.3 RIGHT JOIN

-- RIGHT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
RIGHT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: Returns all orders. Rows for orders that have no matching customer will contain NULL for the customer columns.
The right join is also known as a right outer join.

1.4 FULL OUTER JOIN

-- FULL OUTER JOIN example
SELECT  c.first_name,
        c.last_name,
        c.email,
        c.phone_number,
        o.order_id,
        o.order_date,
        o.book_id
FROM    article.customers   AS c
FULL OUTER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

Result: Returns every row from both tables. Where a match does not exist, the missing side’s columns are filled with NULL.

2️⃣ Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing those rows into a single output value.
Unlike aggregate functions such as SUM() or COUNT() that reduce many rows to one, a window function returns a value for each row in the original result set.

The syntax is built around the OVER() clause:

SELECT  <columns>,
        <window_function>() OVER (
            PARTITION BY <partition_expression>
            ORDER BY <order_expression>
            ROWS BETWEEN <frame_start> AND <frame_end>
        ) AS <alias>
FROM    <table>;

Key components

Categories of window functions

1. Aggregate window functions – Compute aggregates (e.g., SUM(), AVG()) over a window while keeping each row.
2. Ranking window functions – Assign a rank or row number within a partition (e.g., RANK(), DENSE_RANK(), ROW_NUMBER()).
3. Value window functions – Return values from other rows (e.g., LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()).

3️⃣ Example: Ranking Employees by Salary Within Departments

Assume we have an employees table:

3.1 Using RANK()

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rank_by_salary
FROM    article.employees;

What it does

• Partitions rows by department.
• Within each department, orders employees by salary descending.
• Assigns a rank (1 = highest salary).
• If two employees share the same salary, they receive the same rank and the next rank is skipped (standard RANK() behavior).

Result (excerpt)

Ranking restarts for each department.

4️⃣ Example: Average Salary per Department (Aggregate Window Function)

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM    article.employees;

What it does

• Calculates the average salary within each department.
• The average appears on every row, allowing you to compare an individual’s salary to the department average directly.

Result (excerpt)

5️⃣ Further Reading

• Window Functions – Comprehensive guide:
• SQL JOIN Types – Detailed comparison:

From this point forward you can experiment with other window functions (e.g., LEAD(), LAG(), FIRST_VALUE()) to perform advanced analytics without needing nested sub‑queries.

You have now seen what joins and window functions are, the different types available, how they work, and how to write clean, efficient SQL queries to achieve your desired results. Mastering these tools will dramatically improve your ability to analyze, transform, and retrieve data with precision. Start applying them in your own projects today; the more you practice, the more natural they will feel.

Happy querying!