Joins and window functions

Joins
A Join is used to combine rows from two or more tables based on a related column between them.

For example, imagine:

A Customers table containing customer details.

Types of Joins

1. INNER JOIN

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

SELECT c.customer_name, o.order_id
FROM Customers c
INNER JOIN Orders o
    ON c.customer_id = o.customer_id;

1. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. If no match exists, NULL values are returned.

SELECT c.customer_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
    ON c.customer_id = o.customer_id;

1. RIGHT JOIN

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

SELECT c.customer_name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
    ON c.customer_id = o.customer_id;

1. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Non-matching rows get NULLs.

SELECT c.customer_name, o.order_id
FROM Customers c
FULL JOIN Orders o
    ON c.customer_id = o.customer_id;

Why Joins Matter

Joins allow you to:

• Combine normalized data
• Create meaningful reports
• Connect transactional data with master data
• Avoid duplicate data storage

Window Functions
A Window Function performs calculations across a set of table rows that are related to the current row — without collapsing the result into a single row (unlike GROUP BY).

Common Window Functions

1. ROW_NUMBER() Assigns a unique number to each row within a partition.

SELECT employee_name,
       department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM Employees;

Employees are ranked within their department by salary.

1. RANK() Gives ranking but allows ties (skips numbers).

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

1. DENSE_RANK()
   Similar to RANK but does not skip numbers.

2. SUM() as a Window Function
   

SELECT employee_name,
       department,
       salary,
       SUM(salary) OVER (PARTITION BY department) AS department_total
FROM Employees;

Shows total department salary on every row without grouping the data.

1. Running Total Example

SELECT order_date,
       sales_amount,
       SUM(sales_amount) OVER (ORDER BY order_date) AS running_total
FROM Sales;

Joins (In Simple Terms)
Think of joins like merging two Excel sheets using a common column (like ID). If two sheets share a customer ID, you can combine the details into one view.
Without joins, relational databases would not be useful because data is stored separately to avoid duplication.

Window Functions (In Simple Terms)
Window functions are like saying:

“Calculate something for a group, but don’t hide the individual rows.”

For example:

Instead of showing total salary per department (GROUP BY),

You show each employee’s salary AND the department total beside it.

It’s like giving each row extra intelligence.