Understanding Joins and Window Functions

You'll often find that the data you need is spread across multiple tables or
requires complex calculations that simple GROUP BY queries can't handle. Two
of the most powerful tools in your SQL toolbox for solving these challenges are
JOINS and Window Functions.
While Joins allow us to stitch together data from multiple tables, Window
Functions allow us to perform complex calculations across rows without losing
granularity of our data.

This article explains:

• What Joins are
• Types of Joins with examples
• What Window Functions are
• Why Window Functions are powerful
• Sample queries

SQL JOIN

What is a Join?

A Join combines rows from two or more tables using a related column between
them. In a normalized database, data is split into multiple tables to avoid
redundancy. For example, you might have a weather table with city names and a
cities table with detailed city locations. To get a complete picture, you need
to join these tables.

Why do we need Joins?

In relational databases, data is usually split across multiple tables to reduce
duplication and maintain consistency. This process is known as normalization

Types Of Joins

1. Inner Join

This join returns only matching rows from both tables.
If a row in one table has no corresponding row in the other, it is excluded
from the results.

Syntax

SELECT
    columns
FROM
    table1
    INNER JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.name,
    o.amount
FROM
    customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id

2. Left Join

This join returns all rows from the left table, plus matching rows from the
right table. If no match exists, you get NULL values from the right table.
Syntax

SELECT
    columns
FROM
    table1 LEFT
    JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id

3. Right Join

This join returns all rows from the right table, plus matching rows from the
left table. This is the mirrors image of a LEFT JOIN.
Syntax

SELECT
    columns
FROM
    table1
    RIGHT JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    RIGHT JOIN orders o ON c.customer_id = o.customer_id

4. Full Join

Full Join returns all rows from both tables.
If rows from the two tables match based on the join condition they are combined
into a single row. If no match found, the missing columns are filled with NULL
values.
Syntax

SELECT
    columns
FROM
    table1
    FULL JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    FULL JOIN orders o ON c.customer_id = o.customer_id

5. Cross Join

A CROSS JOIN creates a Cartesian product. Every row from the first table paired
with every row from the second table. No condition needed.
Syntax

SELECT
    columns
FROM
    table1
    CROSS JOIN table2

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    CROSS JOIN orders o

WINDOW FUNCTIONS

Window functions allow computations across related rows while preserving
individual rows.
A key difference between GROUP BY and Window Functions is how results are
returned.
GROUP BY collapses multiple rows into a single summary row.
Window Functions performs a calculation across group of rows while returning
each individual row.

Why Window Function Are Powerful

Window functions are widely used in analytics queries because they allow you to
perform complex calculations without losing row-level detail.

General Syntax

window_function([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
    [frame_clause]
)

Types of Window Functions

Window functions are generally divided into three main categories.

1. Aggregate Window Functions
2. Ranking Window Functions
3. Value Window Functions

1. Aggregate Window Functions

These functions perform aggregate across a window of rows.
Common aggregate window functions:

• SUM()
• AVG()
• COUNT()
• MIN()
• MAX()

Example

SELECT
    o.customer_id,
    o.order_date,
    c.first_name,
    c.last_name,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY o.customer_id) AS customer_total
FROM
    orders o
    JOIN customers c ON o.customer_id = c.customer_id;

2. Ranking Window Functions

Ranking functions assign a rank or position to rows based on ordering.

Common ranking functions:

i. ROW_NUMBER() - assign a rank or position to rows based on ordering.

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

ii. RANK() - assigns the same rank for ties but skips numbers.

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

iii. DENSE_RANK() - similar to RANK(), it assigns the same for ties but
does not skip numbers.

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

iv. NTILE() - divides rows into equal buckets.

SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;

3. Value (Navigation) Window Functions

These functions allow you to access values from other rows within the window.

Common navigation functions:

i. LAG() - Returns the value from the previous row.

SELECT
    order_id,
    quantity,
    LAG(quantity) OVER (ORDER BY order_id) AS previous_quantity
FROM orders;

ii. LEAD() - Returns the value from the next row.

SELECT
    order_id,
    quantity,
    LEAD(quantity) OVER (ORDER BY order_id) AS next_quantity
FROM orders;