Joins and Window Functions in SQL

Most people start using SQL to retrieve data. The real shift happens when you begin using it to shape relationships within that data.

1. Joins as Data Logic, Not Just Combination

A join is not just about “bringing tables together”, it’s about defining which relationships matter and which don’t.

Types of Joins
Inner Join - Returns records that have matching values in both tables.
Left Join - Returns all records from them left table, and matched records from the right.
Right Join - Returns all records from the right table, and matched records from the left.
Full Join - Returns all records when there is a match in either table.

Expounding more on Left and Inner Joins:
Left Join
It guarantees that every record from your primary table survives the query, even when there is no matching data in the second table.

For example, in a student system, if you join Students to Borrowed_Books, a Left Join ensures students who have never borrowed a book still appear in your results. Their book-related fields will return NULL, which is not “empty”; it’s meaningful. It tells you no relationship exists.

This is critical in real analysis: drop-offs, non-participation, and missing relationships are often more important than the matches.

Inner Join
It filters your data to only records where a relationship exists in both tables.

Using the same example, only students who have borrowed books will appear. Everyone else is excluded entirely.

This is useful when your analysis depends strictly on confirmed interactions; such as completed transactions, logged activities, or verified matches.

The key difference:

LEFT JOIN answers: “Who exists, regardless of activity?”

INNER JOIN answers: “Who has participated?”

1. Window Functions: Calculations Without Losing Detail

Traditional aggregation functions like SUM() or COUNT() collapse rows into a summary. That’s useful, but limiting.

Window functions solve this by allowing you to compute aggregates while retaining every individual row.

The core idea sits in the OVER() clause.

PARTITION BY: Controlled Context
When you write:

SUM(sales) OVER (PARTITION BY region)

you’re telling SQL:
“Calculate totals within each region, but do not merge the rows.”

Each row still represents an individual transaction—but now carries additional context: the total performance of its group.

Why this matters in practice
Imagine a sales dataset. With a window function, each row can simultaneously show:

• The value of that specific deal
• The total sales for that salesperson’s region
• The ranking of that deal within the region All without losing granularity.

This is what makes window functions powerful. They allow you to move from static summaries to context-aware analysis.