The Two SQL Concepts That Made Me Finally Understand Real Data: Joins & Window Functions.

When I first started learning SQL, I thought I had it figured out.

I could write things like:

SELECT * FROM employees
WHERE salary > 50000;

But the moment I started working with real datasets, things got messy.

Data wasn’t in one table anymore. It was split across multiple tables — employees, departments, salaries, etc.

That’s when two SQL concepts started making real sense to me:

1. Joins
2. Window functions

Once I understood these, SQL felt way more powerful.

In this article, I’ll walk through both concepts using PostgreSQL examples, a small dataset, and the mental models that helped me understand them.

The Example Dataset

Let's imagine a small HR dataset.

employees
employee_id name department_id salary
1 Alice 1 90000
2 Bob 1 80000
3 Carol 2 70000
4 David 2 60000
5 Eve NULL 65000
departments
department_id department_name
1 Engineering
2 Finance
3 Marketing

You can create this dataset in PostgreSQL like this:

CREATE TABLE employees (
employee_id INT,
name TEXT,
department_id INT,
salary INT
);

INSERT INTO employees VALUES
(1,'Alice',1,90000),
(2,'Bob',1,80000),
(3,'Carol',2,70000),
(4,'David',2,60000),
(5,'Eve',NULL,65000);

CREATE TABLE departments (
department_id INT,
department_name TEXT
);

INSERT INTO departments VALUES
(1,'Engineering'),
(2,'Finance'),
(3,'Marketing');

Part 1: Understanding SQL Joins

Joins are how relational databases connect information across tables.

When I first learned joins, the simplest way to understand them was this:

A join links rows from two tables using a common column.

INNER JOIN — Matching Data Only

An INNER JOIN returns only rows where a match exists in both tables.

Example:
show employees and their department names.

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

Result:

name department_name
Alice Engineering
Bob Engineering
Carol Finance
David Finance

Eve disappears because she doesn't have a department assigned.

INNER JOIN only returns matching rows.

LEFT JOIN — Keep All Employees

Sometimes we want all records from one table, even if a match doesn't exist.

That’s where LEFT JOIN becomes useful.

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

Result:

name department_name
Alice Engineering
Bob Engineering
Carol Finance
David Finance
Eve NULL

Now Eve appears, but her department is NULL.

This type of query is extremely common when:

• analyzing incomplete data

• identifying missing relationships

FULL OUTER JOIN — Everything

_A FULL OUTER JOIN _returns everything from both tables.

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

This is very useful for data reconciliation.

Part 2:
WINDOWS FUNCTION

When I first encountered window functions, they looked confusing.

But once I understood the idea, they became one of the most powerful tools in SQL.

The key difference from _GROUP BY _is this:

GROUP BY collapses rows.

Window functions do calculations without removing rows.

*Ranking Employees by Salary
*
Let's say we want to rank employees by salary.

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

_Result:
_
name salary salary_rank
Alice 90000 1
Bob 80000 2
Carol 70000 3
Eve 65000 4
David 60000 5

The keyword OVER() tells SQL that we are doing a window calculation across rows.

Ranking Within Each Department

What if we want to rank employees within their department?

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

Now every department has its own ranking system.
This concept is widely used in analytics for things like:

1. best-selling products per category
2. highest-performing employees per department

Running Totals with Window Functions

Another powerful use case is cumulative calculations.

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

This creates a running total as the rows progress.

These are heavily used in:

1. financial reporting
2. revenue dashboards.

Key Lessons I took away

1 Real data almost always lives in multiple tables

2 Joins are the foundation of relational databases

3 Window functions unlock powerful analytical queries

Final Thoughts

Learning SQL felt straightforward at the beginning.

But when I started working with real datasets, I realized that the real power of SQL lies in:

• connecting datasets
• analyzing patterns within them Joins and window functions are two of the most important tools for doing exactly that.