Understanding Joins and Window Functions

Source: Dev.to

Joins

Joins allow you to combine rows from two or more tables based on a related column.

INNER JOIN

Returns only the rows that have matching values in both tables.

-- 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;

LEFT JOIN

Returns all rows from the left table and the matching rows from the right table. Non‑matching rows from the right table contain NULL.

-- 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;

RIGHT JOIN

Returns all rows from the right table and the matching rows from the left table. Non‑matching rows from the left table contain NULL.

-- 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;

FULL JOIN

Returns all rows when there is a match in either left or right table. Rows without a match in the opposite table contain NULL.

-- 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;

CROSS JOIN

Creates a Cartesian product of the two tables; every row from the first table is paired with every row from the second table.

-- 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 perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows; the original row count is preserved.

General Syntax

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

Categories

Aggregate Window Functions

Perform aggregate calculations over a defined window.

-- Example: total quantity per customer
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;

Common functions: SUM(), AVG(), COUNT(), MIN(), MAX().

Ranking Window Functions

Assign a rank or position to rows based on ordering.

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

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

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

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

Value (Navigation) Window Functions

Access values from other rows within the same window.

-- LAG(): value from the previous row
SELECT
    order_id,
    quantity,
    LAG(quantity) OVER (ORDER BY order_id) AS previous_quantity
FROM orders;

-- LEAD(): value from the next row
SELECT
    order_id,
    quantity,
    LEAD(quantity) OVER (ORDER BY order_id) AS next_quantity
FROM orders;

These functions are especially useful in analytical queries where you need to compare a row to its peers without collapsing the result set.