SQL Joins & Window Functions

Introduction

SQL joins are used to combine data from multiple tables based on a related column.
Window functions , on the other hand perform calculations across a set of table rows related to the current row, enabling row-wise aggregations without collapsing the data
In this article, you will learn in depth how SQL joins and window functions work, when to use them, and how they solve real business problems. Let’s get started.

Types of SQL joins

There are four main types of SQL joins

1. Inner join - returns only the records that have matching values in both tables.

Business Question:

Which customers have placed orders?

Assume we have two tables:

• Customers
• Orders

To answer this question, you'd write this query

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

The query will return this result

Tim Adagala is not in the output because he has no order

1. Left join- returns all records on the left table and only matching rows on the right table

Business Question:

Show all customers, even those who have yet to order.

The query will be:

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

The output

The NULL tells you that Tim Adagala exists but he has not placed any order.

1. Right join- returns all records on the right table and only matching rows on the left table. It is essentially the reverse of a LEFT JOIN.

Business Question: Show all orders and their customers (if available).

This means:

• Keep all orders
• Attach customer info if it exists

Query:

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

Output:

1. Full join - returns all records on both tables regardless of whether there is a match or not

Business Question:

Show all customers and all orders, including unmatched records on both sides.

Query:

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

Output

Window Functions

A window function is a function that perfoms calculation across a set of rows related to the current row, without collapsing the result into a single row like GROUP BY clause.

syntax of a window function

function_name(column)
OVER (
    PARTITION BY column
    ORDER BY column
)

• OVER(): Defines the window of rows to operate on.This is mandatory
• PARTITION BY (Optional): Divides data into logical groups.
• ORDER BY (Sometimes Required): Defines the order of rows inside each partition.

When is ORDER BY Required?

ORDER BY is required for:

• Ranking functions (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE())
• Navigation functions (LAG(), LEAD())

-Running totals

It is not required for simple aggregates like:

SUM(salary) OVER (PARTITION BY department)

Categories of Window Functions

1. Aggregate window functions - uses normal aggregate functions with OVER() Examples: SUM(), AVG(), COUNT(), MIN(), MAX()

Example: SUM(salary) OVER (PARTITION BY department)

1. Ranking functions Examples
   • ROW_NUMBER():Always unique numbering
   • RANK():Skips ranks when ties occur
   • DENSE_RANK():No skipped ranks
   • NTILE():Divides rows into n groups Example:

ROW_NUMBER() OVER (ORDER BY salary DESC)

1. Value/Navigation functions: Used to access data from another row. Examples: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() Example:

LAG(salary) OVER (ORDER BY hire_date)