SQL Joins & Window Functions

Source: Dev.to

Introduction

SQL joins are used to combine data from multiple tables based on a related column.

Window functions 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‑world business problems. Let’s get started.

Types of SQL joins

There are four main types of SQL joins.

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

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;

Result

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

Left join

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

Business Question

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

Query

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

Result

The NULL in order_id indicates that Tim Adagala exists but has not placed any order.

Right join

Returns all records from the right table and only matching rows from the left table (the reverse of a LEFT JOIN).

Business Question

Show all orders and their customers (if available).
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;

Result

Full join

Returns all records from both tables, regardless of whether there is a match.

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 performs calculations across a set of rows related to the current row, without collapsing the result into a single row like a GROUP BY clause.

Syntax of a Window Function

function_name(column)
OVER (
    PARTITION BY column   -- optional
    ORDER BY column       -- sometimes required
)

• OVER() – Defines the window of rows to operate on. This clause is mandatory.
• PARTITION BY – (Optional) Divides data into logical groups.
• ORDER BY – (Sometimes required) Defines the order of rows inside each partition. Required for:
  • Ranking functions
  • Navigation functions
  • Running totals