Kinywa

Posted on Mar 2

SQL Joins and Window Functions

#sql #beginners #data

SQL Joins

What is a Join? A JOIN combines rows from two or more tables based on a related column. They are the backbone of relational databases, spreading data across normalized tables and reuniting it at query time.

Types of Joins

INNER JOIN - returns only rows with a match in both tables.

SELECT e.full_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN - returns all rows from the left table; unmatched right-side rows return NULL.

SELECT e.full_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN - opposite of LEFT JOIN; all rows from the right table are returned.

SELECT e.full_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN - returns all rows from both tables; NULLs fill the gaps where there's no match.

SELECT e.full_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

CROSS JOIN - produces the Cartesian product; every row from table A paired with every row from table B.

SELECT products.name, colors.color
FROM products
CROSS JOIN colors;

SELF JOIN - joins a table with itself; perfect for hierarchical data like org charts.

SELECT e1.full_name AS employee, e2.name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

Example:

-- Sales report combining orders, customers and products
SELECT
    c.name        AS customer,
    p.product_name,
    o.order_date,
    o.quantity,
    (o.quantity * p.price) AS total_value
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products  p ON o.product_id  = p.id
WHERE o.order_date >= '2024-01-01'
ORDER BY total_value DESC;

Window Functions

What Are Window Functions? A Window Function performs calculations across a set of rows related to the current row - its window - without collapsing the result set like GROUP BY does. Every row keeps its identity, the function just adds a computed value beside it.

Key Window Functions with sample queries

ROW_NUMBER() - unique sequential number per partition

SELECT full_name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

RANK() vs DENSE_RANK() - RANK skips numbers on ties while DENSE_RANK does not

SELECT full_name, salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

LAG() / LEAD() - look backwards or forwards without a self-join

SELECT month, revenue,
    LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

NTILE(n) - bucket rows into n equal groups

SELECT name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Summary

Joins at a glance

INNER JOIN - Returns matched rows only
LEFT JOIN - Returns all left rows + matched right (NULLs for no match)
RIGHT JOIN - Returns all right rows + matched left (NULLs for no match)
FULL OUTER JOIN - Returns all rows from both tables, NULLs where unmatched
CROSS JOIN - Returns every row combination (Cartesian product)
SELF JOIN - Returns a table joined to itself

Window Functions at a glance

ROW_NUMBER() - Unique sequential ID per row/partition
RANK() / DENSE_RANK() - Rankings with/without tie gaps
LAG() / LEAD() - Compare current row to prior/next row
SUM() / AVG() as window - Running totals, moving averages
NTILE(n) - Percentile buckets
FIRST_VALUE() / LAST_VALUE() - First or last value in a window

Joins are like smart VLOOKUPs - they stitch data from multiple tables together using a shared key.

Window Functions are like adding a calculated column to a spreadsheet that is "aware" of its neighbors.

DEV Community