PHILIP KAPLONG (Sirphilip)

Posted on Mar 3

SQL Joins and Window Functions- Ultimate Guide

#data #analytics #sqlserver #database

SQL JOINS

What is a JOIN?

A JOIN is used to combine data from two or more tables based on a related column. Think of it like connecting two lists: one of employees, another of departments. You use a common field (like department_id) to link them.

Types of Joins

INNER JOIN

Returns only the rows that have matching values in both tables.
Example: Only employees assigned to a department will appear; employees without a department are ignored.
Syntax:

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

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table.
If there’s no match in the right table, it returns NULL.
Example: All employees appear, even if they don’t belong to any department.

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

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table.
If there’s no match in the left table, it returns NULL.
Example: All departments appear, even if no employee is assigned.

SELECT name, department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

FULL OUTER JOIN

Returns all rows from both tables, filling in NULLs where no match exists.
Example: Employees without departments and departments without employees will all appear.

SELECT name, department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;

CROSS JOIN

Returns every possible combination of rows from both tables.
Example: 3 employees × 3 departments = 9 rows.
Use with caution as it can create very large results.

SELECT *
FROM employees
CROSS JOIN departments;

NATURAL JOIN

Automatically joins tables using all columns with the same name.
You don’t write the ON condition.
Example: Employees and departments both have department_id. SQL will join them automatically using this column.

SELECT *
FROM employees
NATURAL JOIN departments;

Warning: If a new column with the same name is added later, the join might change results unexpectedly. Use explicit joins in real systems for safety.

WINDOW FUNCTIONS

What is a Window Function?

A window function performs calculations across rows related to the current row while keeping all original rows intact. Unlike GROUP BY, which reduces rows to summaries, window functions add new computed columns without removing data.

Basic Syntax

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

OVER() → indicates a window function.
PARTITION BY → divides data into groups.
ORDER BY → sets the order of rows within each group.

Ranking Functions

ROW_NUMBER()

Assigns a unique number to each row in the result.
No ties are allowed; every row is unique.

SELECT name,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

RANK()

Assigns the same rank to ties, but skips numbers for the next rank.
Example: Two employees with the same salary share rank 1, next rank becomes 3.

SELECT name,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK()

Assigns the same rank to ties but does not skip numbers.
Example: Two employees share rank 1, next rank is 2.

SELECT name,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

SUM() OVER() — Running Totals

Calculates cumulative totals while keeping all rows.
Example: Adding salaries cumulatively for a payroll report.

SELECT name,
       salary,
       SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;

PARTITION + ORDER TOGETHER

You can rank or sum values within a group.
Example: Ranking employees per department by salary.

SELECT name,
       department_id,
       salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Ranking resets for each department.
Useful for finding top performers per group.

When to Use What

JOINS → Combine multiple tables for connected data.
GROUP BY → Summarize data, reduces rows.
WINDOW FUNCTIONS → Keep rows and perform calculations like ranking, running totals, or comparisons within groups.

Key Takeaways

Joins connect tables:

INNER → only matching rows
LEFT → keep all left rows
RIGHT → keep all right rows
FULL → keep everything
NATURAL → automatic join on same-named columns

GROUP BY → reduces rows to summary data.
Window Functions → keep all rows, add extra calculations:

PARTITION BY → group rows inside the function
ORDER BY → controls calculation order
ROW_NUMBER / RANK / DENSE_RANK → ranking functions
SUM() OVER → running totals

DEV Community