A TECHNICAL DEEP DIVE INTO SQL JOINS AND WINDOWS FUNCTIONS.

Relational databases are built on relationships between tables. Joins and window functions enable data retrieval and analytics. Joins combine data across tables, window functions enable row-level analytics without collapsing result sets.

SQL joins

what is a join?
A join combines rows from two or more tables based on a related column (usually a foreign key)

Types of joins

INNER JOIN

Returns only matching rows from both tables
eg ; show employees with their department name.
Only matching rows appear. If an employee had no department, they would be excluded.

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

LEFT JOIN

Returns all rows from the left table and matched rows from the right table. Non matches become NULL.

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

Explanation; every department appears. If a department has no employees, employee columns are NULL.

RIGHT JOIN

Returns all rows from the right table and matches rows from the left table.

Similar to a left join. The only difference is the right table is the base table

FULL OUTER JOIN

Returns all matching rows between two tables, all non matching rows from the left table, and all non matching rows from the right table.
where there's no match, the missing side appears as NULL.

• Example Scenario Assume:

1. Some departments have no employees
2. Some employees are incorrectly assigned to non-existent departments
3. Some projects exist without employees assigned

A FULL OUTER JOIN helps detect data gaps.

SELECT 
    d.department_name,
    e.name AS employee_name
FROM departments d
FULL OUTER JOIN employees e
    ON d.department_id = e.department_id;

CROSS JOIN

Join operation that combines every row from the first table with every row from the second table, producing a result known as the Cartesian product. Unlike other join types (like INNER JOIN or LEFT JOIN), it does not require a join condition using an ON clause.

• If We Have: 4 employees 3 projects Result = 12 rows

SELECT 
    e.name,
    p.project_name
FROM employees e
CROSS JOIN projects p;

WINDOW FUNCTIONS

These are the most powerful analytical features in SQL. They allow you to perform calculations across related rows while preserving individual row detail.

basic syntax;

FUNCTION() OVER (
    PARTITION BY column
    ORDER BY column
)

Key components:

• OVER() → defines the window
• PARTITION BY → divides rows into groups
• ORDER BY → defines ordering within the group

RANKING EMPLOYEES WITHIN DEPARTMENTS

SELECT 
    e.name,
    d.department_name,
    e.salary,
    RANK() OVER (
        PARTITION BY d.department_name
        ORDER BY e.salary DESC
    ) AS salary_rank
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

• What happens

1. Data is split by department
2. within each department, employees are ordered by salary
3. A rank is assigned.

When to use window functions

Use them when you need;

• Ranking within groups
• Running totals
• Moving averages
• Department comparisons
• Row-level analytics
• Gap analysis

Window functions analyze rows without collapsing them.

1. Partition by defines grouping logic
2. Order by defines sequence for calculation
3. RANK(), ROW_NUMBER(), DENSE_RANK() handle ranking.
4. SUM(), AVG() OVER() enable contextual aggregation.
5. They are essential for analytical reporting.

Summary

Joins connect data while window functions analyze connected data.